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.
Task #3a. Non-CDB database backup using default RMAN configuration.
Task #3b. Non-CDB database backup using a modified RMAN configuration.
Task #3c. CDB database backup using a customized OS RMAN backup script.
Task #3d. PDB database backup using Oracle Enterprise Manager.
Task #3e. Customized OS RMAN backup script scheduled via OEM.
Task #4: Execute RMAN Restores & Recoveries.
Task #4a. Point of Failure (Complete) Restore/Recovery of a CDB/non-CDB.
Task #4b. Point of Failure (Complete) Restore/Recovery of a PDB.
Task #4c. Point In Time (Incomplete) Restore/Recovery of a CDB/non-CDB.
Task #4d. Point In Time (Incomplete) Restore/Recovery of a PDB.
Task #5: More Advanced Uses of RMAN.
Task #5a. Point of Failure (Complete) PDB Data File Restore/Recovery.
Task #5b. Point In Time Database Restore/Recovery to a New Server (ASM to File System).
Task #5c. Point In Time Database Restore/Recovery to a New Server (File System to ASM).
(coming soon)
Task #5d. Tablespace Point In Time Restore/Recovery. (coming soon)
Task #5e. Table Restore/Recovery. (coming soon)
Task #5f. Active Duplication. (coming soon)
Task #6: Additional RMAN Features. (coming soon)
Task #6a. RMAN RESTORE VALIDATE Command.
Task #6b. RMAN RESTORE PREVIEW Command.
Task #6c. RMAN VALIDATE Command.
Task #6d. RMAN RESTORE VALIDATE CHECK LOGICAL Command.
Task #6e. RMAN Reporting.
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 tasks will cover the following topics (click the link you need):
Task #3a. Non-CDB database backup using a default RMAN configuration.
Task #3b. Non-CDB database backup using a modified RMAN configuration.
Task #3c. CDB database backup using a customized OS RMAN backup script.
Task #3d. PDB database backup using Oracle Enterprise Manager.
Task #3e. Customized OS RMAN backup script scheduled via OEM.
Task #3a. 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
Task #3b. Non-CDB Database Backup using a Modified RMAN Configuration.
As we saw in Task #3a, 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
Task #3c. 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
Task #3d. 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.
Task #3e. Customized OS RMAN Backup Script Scheduled Via OEM.
Using OEM to generate an RMAN script is all well and good, but what you end up with might not be exactly what you want. Often because it does not take into account all the idiosyncrasies of your particular environment. You can edit the generated script via OEM, but then you can’t go back and change any of the settings you selected to help OEM generate the script you now want to change. Also, if you’re going to edit the script anyway, why not just write your own and have complete control over what it does from the start? This is what many DBAs tend to do and that’s what we’ll do next.
Apart from being a bit flaky (thanks to Java) the other major obstacle to using OEM is its steep learning curve. It is probably this reason why many DBAs still prefer to use the operating system (e.g. cron) to schedule database related jobs, including backups. The job scheduling and notification mechanism built into the database and accessed via OEM is simpler and more friendly than you might expect. Surprisingly. Scheduling backups via OEM makes sense for a couple of important reasons. First, anything which touches the database should be controlled by the database (IMO). That way you only have one place to look for all scheduled job activity which impacts the database infrastructure. Second, by using OEM you can leverage the ‘black out’ functionality so you can guarantee no database job activity during periods of maintenance.
What you backup, how you backup and when you backup has everything to do with two important concepts. They are Recovery Time Objective (RTO) and Recovery Point Objective (RPO). RTO establishes how much time is allowed for database recovery in the event of a failure. RPO establishes how much data loss is tolerable in the event of a failure. Both these values are determined by the business, not by the IT Department or the DBA. If you ever get these answers from the business, chances are they’ll be zero down time and zero data loss. That’s fine, but building an infrastracture to achieve that is spendy and that’s when you find out they don’t have the budget for it. 😄 Enter the art of the compromise. A common backup schedule is a weekly full database backup with daily archived redo log backups. That’s what we’ll run through here.
First, you’ll need a backup script. This is one I wrote which can handle single instance non-CDBs, CDBs, PDBs and archived redo log backups. Here’s the script’s help output to show how we’ll use the script:
[oracle@orasvr01 backup]$ pwd /nas/scripts/backup [oracle@orasvr01 backup]$ ./rman_online_backup.sh -help rman_backup.sh: Usage (must be run as the oracle user) rman_online_backup.sh -help Displays this help message rman_online_backup.sh -NONCDB <NON_CDB_NAME> Runs a full non-CDB backup rman_online_backup.sh -CDB <CDB_NAME> Runs a full CDB backup plus all PDBs rman_online_backup.sh -CDB <CDB_NAME> -PDB <PDB_NAME> Runs a full PDB backup rman_online_backup.sh -LOGS <NON_CDB_NAME> | <CDB_NAME> Runs an archived redo log backup
A few points to note:
- This script is accessible from all my servers via NFS, so there’s only one copy to maintain in a single location.
- Running a backup of a CDB and a non-CDB is pretty much the same operation, so the script could be simplified even further.
- Running a backup of a PDB can be done at the CDB level or at the PDB level. Backing up at the CDB level does change the syntax a little, but you can backup multiple PDBs at the same time (this script backs up single PDBs). Backing up at the PDB level keeps the backup command syntax consistent, but you cannot connect to a recovery catalog from a PDB and you cannot backup the CDB’s archived redo logs.
- For email notifications to work, you need to do two things. First, setup the Outgoing Mail (SMTP) Server by going here: Setup ➡️ Notifications ➡️ Mail Servers. Second, setup your email address by going here: Setup ➡️ Security ➡️ Administrators.
The method to create a job to run the backup script is the same whether the backup is a non-CDB, CDB, PDB or archived redo logs. Only the script parameters and schedule will be different. That being the case, we’ll run through setting up a backup of the T183 CDB running on orasvr02, to occur each Saturday at 8PM. From the host (orasvr02) home page, navigate to Host ➡️ Job Activity:
I tested the script at the OS command line before I scheduled it via OEM. However, I wanted to test OEM’s ability to run the job for me. I could not find an option to run it immediately, so instead I created a copy of the job and changed its schedule to One Time (Immediately) on the Schedule tab. This is the screen which you can use to monitor the script’s progress:
Notice how the script’s output is written to the Output section. The actual RMAN output is written to the log file defined in the script itself (log=${LOG_FILE}). The script’s output is also included in the email notification. Before we end this section, let’s test OEM when the script detects something is wrong and errors out. A simple test case was to comment out the T183 entry in /etc/oratab, so it looks to the script like that database is not defined on the orasvr02 server. Here’s the same screen dealing with that error condition:
The error message from the script is also included in the email notification. There you have it. How to setup a backup schedule in OEM using your own customized script which emails you when it works and when it doesn’t. Next we will be leveraging the whole point of backing up – database restore and recovery.
Task #4: Execute RMAN Restores & Recoveries.
Now comes the fun part. Actually using your backups to restore and recover databases. In this section, we’ll cover the 4 most common restore and recovery scenarios you’re likeky to encounter. Before we get to those, let’s quickly re-cap our database environment so we know what we’re dealing with:
• T122 is a non-CDB Oracle Database 12c Release 2 database using standard file system storage.
• T122 uses a Fast Recovery Area, /u07/oradata/fast_recovery_area/T122.
• T122’s full database and archived redo log backups are written to /nas/backups/T122.
• T183 is a CDB Oracle Database 18c Release 3 database using ASM storage.
• T183 contains 1 PDB called either T183_PDB1 or T183_PDB2.
• T183 uses a Fast Recovery Area, +RECO.
• T183’s full database and archived redo log backups are written to /nas/backups/T183.
• T122 runs on server orasvr01 and T183 runs on server orasvr02.
• All databases are backed up once a week at 8PM on Saturdays.
• All database archive redo log files are backed up daily at 11PM.
In this task we will cover these restore and recovery scenarios (click the link you need):
Task #4a. Point of Failure (Complete) Restore/Recovery of a CDB/non-CDB.
Task #4b. Point of Failure (Complete) Restore/Recovery of a PDB.
Task #4c. Point In Time (Incomplete) Restore/Recovery of a CDB/non-CDB.
Task #4d. Point In Time (Incomplete) Restore/Recovery of a PDB.
Task 4a. Point of Failure (Complete) Restore/Recovery of a CDB/non-CDB.
For this task I’ll use the non-CDB database, T122. To simulate a database failure I will shutdown the instance, rename the instance parameter file, the database control files and all the data files, then attempt to restart the instance which will, of course, fail.These are the files I will rename:
SQL> show parameter pfile
NAME TYPE VALUE
---------------------- ----------- ----------------------------------------------------------
spfile string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileT122.ora
SQL> show parameter control_files
NAME TYPE VALUE
------------------ ---------- --------------------------------------------------------------------
control_files string /u02/oradata/T122/controlfile/o1_mf_gwykp3gt_.ctl
/u07/oradata/fast_recovery_area/T122/controlfile/o1_mf_gwykp3ob_.ctl
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------
/u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf
/u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf
/u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbf
/u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf
Step #1: Assess The Situation.
Before you do anything, find out what’s broken/missing and what’s still left of the database. This will help determine your approach to restoring and recovering the database using the least resource in the fastest time. It will also help prime you for what to expect from RMAN’s output:
| File Type | Location | Status |
|---|---|---|
| Parameter File | $ORACLE_HOME/dbs/spfileT122.ora | Missing |
| Control Files | /u02/oradata/T122/controlfile/o1_mf_gwykp3gt_.ctl | Missing |
| /u07/oradata/fast_recovery_area/T122/controlfile/o1_mf_gwykp3ob_.ctl | Missing | |
| Data Files | /u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf | Missing |
| /u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf | Missing | |
| /u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbf | Missing | |
| /u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf | Missing | |
| Online Redo Log Files | /u02/oradata/T122/onlinelog/o1_mf_3_gwykp67m_.log | Present |
| /u07/oradata/fast_recovery_area/T122/onlinelog/o1_mf_3_gwykp6ly_.log | Present | |
| /u02/oradata/T122/onlinelog/o1_mf_2_gwykp65r_.log | Present | |
| /u07/oradata/fast_recovery_area/T122/onlinelog/o1_mf_2_gwykp6ty_.log | Present | |
| /u02/oradata/T122/onlinelog/o1_mf_1_gwykp63x_.log | Present | |
| /u07/oradata/fast_recovery_area/T122/onlinelog/o1_mf_1_gwykp6q5_.log | Present | |
| Temporary Files | /u02/oradata/T122/datafile/o1_mf_temp_gwykpsn3_.tmp | Present |
| Archived Redo Log Files | /u07/oradata/fast_recovery_area/* | Present |
| Backup Files | /nas/backups/T122/* | Present |
We know the online redo log files are present so we know all unarchived transactions are intact. That means we know we’ll be able to perform a complete restore and recovery up to the point of failure. Let’s try to start the instance just for a laugh:
[oracle@orasvr01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 31 12:27:42 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initT122.ora'
Step #2: Restore the SPFILE.
We know the controlfile is set to autobackup which has the effect of also backing up the SPFILE. Since we’re using a FRA, the control file backup should be in a standard place since Oracle manages the content and structure of the FRA. Therefore, the latest control file backup should be in the most recent directory within /u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30:
[oracle@orasvr01 2020_01_30]$ pwd /u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30 [oracle@orasvr01 2020_01_30]$ ls -l -rw-r----- 1 oracle oinstall 10731520 Jan 30 23:02 o1_mf_s_1031094118_h37f6l7j_.bkp
We can verify this backup piece file does contain the SPFILE by scanning it for text:
[oracle@orasvr01 2020_01_30]$ strings o1_mf_s_1031094118_h37f6l7j_.bkp | more
}|{z
T122
e?u=
TAG20200130T230155
f?u=
T122
T122.__data_transfer_cache_size=0
T122.__db_cache_size=1509949440
T122.__inmemory_ext_roarea=0
T122.__inmemory_ext_rwarea=0
T122.__java_pool_size=16777216
T122.__large_pool_size=33554432
T122.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
T122.__pga_aggregate_target=1694498816
T122.__sga_target=2147483648
T122.__shared_io_pool_size=117440512
T122.__shared_pool_size=436207616
T122.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/T122/adump'
*.audit_trail='db'
*.compatible='12.2.0'
Yep, that looks like what you’d expect to see in an SPFILE. Let’s start an RMAN session and restore the SPFILE. If you’re using a Recovery Catalog (which we are), you must explicitly set the DBID of the target database otherwise the catalog won’t know what database you’re talking about. You also need to run a startup command to get the instance to start with no parameter file before you can restore the parameter file:
[oracle@orasvr01 T122]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jan 31 13:45:12 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set DBID = 2185934179; executing command: SET DBID RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database recovery catalog schema release 18.03.00.00. is newer than RMAN release RMAN> startup; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initT122.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 01/31/2020 13:45:31 ORA-00205: error in identifying control file, check alert log for more info RMAN> restore spfile; Starting restore at 31-JAN-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=175 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=176 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring SPFILE output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileT122.ora channel ORA_DISK_1: reading from backup piece /u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30/o1_mf_s_1031094118_h37f6l7j_.bkp channel ORA_DISK_1: piece handle=/u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30/o1_mf_s_1031094118_h37f6l7j_.bkp tag=TAG20200130T230155 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 31-JAN-20
Let’s check to make sure it’s back:
[oracle@orasvr01 ~]$ cd $ORACLE_HOME/dbs
[oracle@orasvr01 dbs]$ ls -l
-rw-rw---- 1 oracle oinstall 1544 Jan 31 13:45 hc_T122.dat
-rw-r--r-- 1 oracle oinstall 3079 May 15 2015 init.ora
-rw-r----- 1 oracle oinstall 24 Jan 31 13:45 lkDUMMY
-rw-r----- 1 oracle oinstall 24 Nov 15 18:43 lkT122
-rw-r----- 1 oracle oinstall 4608 Dec 3 08:40 orapwT122
-rw-r----- 1 oracle oinstall 10633216 Dec 4 09:47 snapcf_T122.f
-rw-r----- 1 oracle oinstall 3584 Jan 31 15:35 spfileT122.ora
-rw-r----- 1 oracle oinstall 3584 Jan 31 13:28 spfileT122.ora.ORIG
Step #3: Restore the Control Files.
Now the SPFILE has been restored, we need to re-start the instance using it, but not mount the database (because the data files are still missing). Then we can restore the control files:
RMAN> startup force nomount; Oracle instance started Total System Global Area 2147483648 bytes Fixed Size 8622776 bytes Variable Size 603983176 bytes Database Buffers 1526726656 bytes Redo Buffers 8151040 bytes RMAN> restore controlfile; Starting restore at 31-JAN-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=237 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=19 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30/o1_mf_s_1031094118_h37f6l7j_.bkp channel ORA_DISK_1: piece handle=/u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30/o1_mf_s_1031094118_h37f6l7j_.bkp tag=TAG20200130T230155 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 output file name=/u02/oradata/T122/controlfile/o1_mf_gwykp3gt_.ctl output file name=/u07/oradata/fast_recovery_area/T122/controlfile/o1_mf_gwykp3ob_.ctl Finished restore at 31-JAN-20
Let’s check to make sure they’re back:
[oracle@orasvr01 dbs]$ ls -l /u02/oradata/T122/controlfile/ -rw-r----- 1 oracle oinstall 10633216 Jan 31 15:43 o1_mf_gwykp3gt_.ctl -rw-r----- 1 oracle oinstall 10633216 Jan 31 13:31 o1_mf_gwykp3gt_.ctl.ORIG [oracle@orasvr01 dbs]$ ls -l /u07/oradata/fast_recovery_area/T122/controlfile/ -rw-r----- 1 oracle oinstall 10633216 Jan 31 15:43 o1_mf_gwykp3ob_.ctl -rw-r----- 1 oracle oinstall 10633216 Jan 31 13:31 o1_mf_gwykp3ob_.ctl.ORIG
Step #4: Restore the Database.
Next, we’ll mount the database and restore the data files:
RMAN> alter database mount; Statement processed RMAN> restore database; Starting restore at 31-JAN-20 Starting implicit crosscheck backup at 31-JAN-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=258 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=23 device type=DISK Crosschecked 44 objects Finished implicit crosscheck backup at 31-JAN-20 Starting implicit crosscheck copy at 31-JAN-20 using channel ORA_DISK_1 using channel ORA_DISK_2 Finished implicit crosscheck copy at 31-JAN-20 searching for all files in the recovery area cataloging files… cataloging done List of Cataloged Files File Name: /u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_194_h37o4s6f_.arc File Name: /u07/oradata/fast_recovery_area/T122/autobackup/2020_01_30/o1_mf_s_1031094118_h37f6l7j_.bkp using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/T122/datafile/o1_mf_system_gwykl8n7_.dbf channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/T122/datafile/o1_mf_undotbs1_gwyko31q_.dbf channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3iumsvdk_1_1.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00003 to /u02/oradata/T122/datafile/o1_mf_sysaux_gwykmzwz_.dbf channel ORA_DISK_2: restoring datafile 00007 to /u02/oradata/T122/datafile/o1_mf_users_gwyko450_.dbf channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3humsvdj_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3iumsvdk_1_1.bkp tag=TAG20200125T200151 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:15:16 channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3humsvdj_1_1.bkp tag=TAG20200125T200151 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:15:16 Finished restore at 31-JAN-20
Let’s check to make sure the data files are back. Notice how the restore gives the data files different names from the originals. This is OMF at work:
[oracle@orasvr01 datafile]$ pwd /u02/oradata/T122/datafile [oracle@orasvr01 datafile]$ ls -l -rw-r----- 1 oracle oinstall 1761615872 Jan 31 13:31 o1_mf_sysaux_gwykmzwz_.dbf.ORIG -rw-r----- 1 oracle oinstall 1761615872 Jan 31 15:54 o1_mf_sysaux_h398czd6_.dbf -rw-r----- 1 oracle oinstall 880812032 Jan 31 13:31 o1_mf_system_gwykl8n7_.dbf.ORIG -rw-r----- 1 oracle oinstall 880812032 Jan 31 15:53 o1_mf_system_h398cz46_.dbf -rw-r----- 1 oracle oinstall 33562624 Jan 31 01:17 o1_mf_temp_gwykpsn3_.tmp -rw-r----- 1 oracle oinstall 136323072 Jan 31 13:31 o1_mf_undotbs1_gwyko31q_.dbf.ORIG -rw-r----- 1 oracle oinstall 136323072 Jan 31 15:52 o1_mf_undotbs1_h398d01w_.dbf -rw-r----- 1 oracle oinstall 6561792 Jan 31 13:31 o1_mf_users_gwyko450_.dbf.ORIG -rw-r----- 1 oracle oinstall 6561792 Jan 31 15:52 o1_mf_users_h398d0dl_.dbf
Step #5: Recover the Database.
The data files have been restored to the state of the most recent backup almost a week ago. The next step will apply all the (backed up) archived redo log files to effectively roll the database forward up to (almost) the present time. Then the unbacked up archived redo log files will be applied. Finally, any unarchived transactions held in the online redo log files will be applied and the recovery will be complete.
The following output is quite long and verbose, but a big part of getting comfortable with RMAN is getting used to its output.
RMAN> recover database; Starting recover at 31-JAN-20 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery archived log for thread 1 with sequence 193 is already on disk as file /u07/oradata/fast_recovery_area/T122/onlinelog/o1_mf_1_gwykp6q5_.log archived log for thread 1 with sequence 194 is already on disk as file /u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_194_h37o4s6f_.arc archived log for thread 1 with sequence 195 is already on disk as file /u02/oradata/T122/onlinelog/o1_mf_3_gwykp67m_.log channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=175 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3jumt0d5_1_1.bkp channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=176 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-25/2020-01-25_23:00/T122_3lumt9to_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-25/2020-01-25_20:00/T122_3jumt0d5_1_1.bkp tag=TAG20200125T201844 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_175_h39cwrhv_.arc thread=1 sequence=175 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_175_h39cwrhv_.arc RECID=201 STAMP=1031158333 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=177 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=178 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3oumvu95_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-25/2020-01-25_23:00/T122_3lumt9to_1_1.bkp tag=TAG20200125T230108 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:04:58 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_176_h39cwxwc_.arc thread=1 sequence=176 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_176_h39cwxwc_.arc RECID=202 STAMP=1031158368 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=179 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=180 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3numvu94_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3oumvu95_1_1.bkp tag=TAG20200126T230051 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:14:07 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_177_h39d6671_.arc thread=1 sequence=177 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_177_h39d6671_.arc RECID=203 STAMP=1031158759 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_178_h39d63bd_.arc thread=1 sequence=178 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_178_h39d63bd_.arc RECID=204 STAMP=1031158762 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=181 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3pumvu97_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3numvu94_1_1.bkp tag=TAG20200126T230051 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:33:33 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_179_h39f0oo1_.arc thread=1 sequence=179 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_179_h39f0oo1_.arc RECID=205 STAMP=1031159623 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_180_h39f0l29_.arc thread=1 sequence=180 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_180_h39f0l29_.arc RECID=206 STAMP=1031159633 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=182 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-27/2020-01-27_23:00/T122_3run2ilr_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-26/2020-01-26_23:00/T122_3pumvu97_1_1.bkp tag=TAG20200126T230051 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:31:03 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_181_h39gzgrn_.arc thread=1 sequence=181 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_181_h39gzgrn_.arc RECID=207 STAMP=1031161497 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=183 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-27/2020-01-27_23:00/T122_3sun2ils_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-27/2020-01-27_23:00/T122_3run2ilr_1_1.bkp tag=TAG20200127T230112 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:05:09 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_182_h39jsolk_.arc thread=1 sequence=182 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_182_h39jsolk_.arc RECID=208 STAMP=1031163415 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=184 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=185 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-28/2020-01-28_23:00/T122_3uun570p_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-27/2020-01-27_23:00/T122_3sun2ils_1_1.bkp tag=TAG20200127T230112 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:16:51 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_183_h39k3bmb_.arc thread=1 sequence=183 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_183_h39k3bmb_.arc RECID=209 STAMP=1031163700 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=186 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=187 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-28/2020-01-28_23:00/T122_3vun570t_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-28/2020-01-28_23:00/T122_3uun570p_1_1.bkp tag=TAG20200128T230039 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:15:08 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_184_h39l2x8d_.arc thread=1 sequence=184 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_184_h39l2x8d_.arc RECID=211 STAMP=1031164809 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_185_h39l302f_.arc thread=1 sequence=185 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_185_h39l302f_.arc RECID=210 STAMP=1031164806 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=188 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=189 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-29/2020-01-29_23:00/T122_41un7rco_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-28/2020-01-28_23:00/T122_3vun570t_1_1.bkp tag=TAG20200128T230039 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:29:16 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_186_h39lz8sn_.arc thread=1 sequence=186 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_186_h39lz8sn_.arc RECID=213 STAMP=1031165665 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_187_h39lzd0n_.arc thread=1 sequence=187 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_187_h39lzd0n_.arc RECID=212 STAMP=1031165583 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=190 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=191 channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-01-29/2020-01-29_23:00/T122_42un7rcp_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-29/2020-01-29_23:00/T122_41un7rco_1_1.bkp tag=TAG20200129T230038 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:18:26 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_188_h39np83r_.arc thread=1 sequence=188 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_188_h39np83r_.arc RECID=214 STAMP=1031167483 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_189_h39np4pj_.arc thread=1 sequence=189 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_189_h39np4pj_.arc RECID=215 STAMP=1031167486 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=192 channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-01-30/2020-01-30_23:00/T122_44unafqh_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-01-29/2020-01-29_23:00/T122_42un7rcp_1_1.bkp tag=TAG20200129T230038 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:30:24 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_190_h39orq5q_.arc thread=1 sequence=190 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_190_h39orq5q_.arc RECID=217 STAMP=1031168518 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_191_h39ortcz_.arc thread=1 sequence=191 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_191_h39ortcz_.arc RECID=216 STAMP=1031168445 channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-01-30/2020-01-30_23:00/T122_44unafqh_1_1.bkp tag=TAG20200130T230135 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:18:27 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_192_h39qkp02_.arc thread=1 sequence=192 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_192_h39qkp02_.arc RECID=218 STAMP=1031170336 archived log file name=/u07/oradata/fast_recovery_area/T122/onlinelog/o1_mf_1_gwykp6q5_.log thread=1 sequence=193 archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_01_31/o1_mf_1_194_h37o4s6f_.arc thread=1 sequence=194 archived log file name=/u02/oradata/T122/onlinelog/o1_mf_3_gwykp67m_.log thread=1 sequence=195 media recovery complete, elapsed time: 00:57:59 Finished recover at 31-JAN-20
Step #6: Open the Database.
Once the recovery has been completed, it just remains to open the database:
RMAN> alter database open resetlogs; Statement processed new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete
Note, the RESETLOGS option was needed since the control file was restored from backup. If we had not needed a restored control file, we would not have needed to open the database with the RESETLOGS option. Whenever RESETLOGS is used, Oracle creates a new incarnation or version of the database which is simply identified by a number starting with 1. When a RESETLOGS operation is executed by the database, a number of things happen including the archive of the online redo log, after which their contents are erased and the log sequence number is reset to 1.
Oracle does this so it can keep track of the SCN ranges within a given database incarnation in the event a database restore is required. Different incarnations of the database can have the same range of SCNs, so it’s important to know which incarnation you want to restore from. For example:
Incarnation #1 contains SCNs from 1 to 100. An incomplete/point-in-time database restore and recovery takes the database back to SCN 50. This activity requires a RESETLOGS operation to open the database and that creates a new incarnation. Transactions continue and the new incarnation’s SCN increases from 50 to 200. So now we have incarnation #1 which contained SCNs 1 to 100 and current incarnation #2 which contains SCNs 50 to 200. What happens if you subsequently wanted to restore/recover the database back to SCN 75? Which SCN 75? The original SCN 75 contained within incarnation #1 or SCN 75 contained within the currect incarnation #2? By default, RMAN would use the current incarnation (#2) and take the database back from SCN 200 to SCN 75. However, suppose you discovered the original restore/recovery which took the database back to SCN 50 actually went back too far and you needed to only go back to SCN 75. That SCN 75 is within incarnation #1. So how do we go back to that SCN rather than SCN 75 within the current incarnation? Easy. Within RMAN, simply issue a RESET DATABASE TO INCARNATION N command, where N is the number of the incarnation you need:
RMAN> reset database to incarnation 1; RMAN> recover database to scn 75;
This does assume you still have access to all the backups and archived redo log files needed to take the database back to incarnation 1’s SCN 75. 🙂 You can get information about database incarnations from the data dictionary view V$DATABASE_INCARNATION or ask RMAN:
RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- ------- --------- ----------- 1 16 T122 2185934179 PARENT 1 26-JAN-17 1 2 T122 2185934179 PARENT 1408558 15-NOV-19 1 5899 T122 2185934179 CURRENT 6761449 31-JAN-20
Task #4b. Point of Failure (Complete) Restore/Recovery of a PDB.
For this example we’ll use the PDB, T183_PDB2 within the CDB T183. This server runs an ASM instance which won’t allow me to delete the datafiles while the PDB is open. Therefore, to simulate a problem with the PDB, I will close the PDB, delete all 4 datafiles, then attempt to re-open the PDB. Here are the datafiles:
ASMCMD> pwd +data/T183/9DA0CE150DED43F7E0531200A8C0ECEC/datafile ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE FEB 03 08:00:00 Y SYSAUX.266.1031338697 DATAFILE UNPROT COARSE FEB 03 08:00:00 Y SYSTEM.257.1031331865 DATAFILE UNPROT COARSE FEB 03 08:00:00 Y UNDOTBS1.258.1031331863 DATAFILE UNPROT COARSE FEB 03 08:00:00 Y USERS.259.1031338701
Let’s re-start the PDB and see what it does:
SQL> alter pluggable database t183_pdb2 open; alter pluggable database t183_pdb2 open * ERROR at line 1: ORA-01157: cannot identify/lock data file 15 - see DBWR trace file ORA-01110: data file 15: '+DATA/T183/9DA0CE150DED43F7E0531200A8C0ECEC/DATAFILE/undotbs1.258.1031331863'
Step #1: Assess the Situation.
Always figure out what’s broken and what you still have. Since this is a CDB we know the instance is up and running and all other database operations are functioning normally. If we check the relevant ASM directory we see the PDB datafiles are missing:
ASMCMD> pwd +data/T183/9DA0CE150DED43F7E0531200A8C0ECEC/datafile ASMCMD> ls -l ASMCMD-8002: entry 'datafile' does not exist in directory '+data/T183/9DA0CE150DED43F7E0531200A8C0ECEC/'
We need to restore and recover the whole PDB up to the point of failure. The key to this recovery is to use RMAN to connect to the CDB, but not connect to the Recovery Catalog. If you do, the PDB restore and recovery won’t work. The PDB needs to be closed for the datafile restore and recovery. Since we could not open the PDB, we know it’s closed.
Step #2: Restore the PDB.
[oracle@orasvr02 2020-02-02]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Mon Feb 3 08:56:16 2020 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> restore pluggable database t183_pdb2; Starting restore at 03-FEB-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=72 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=268 device type=DISK allocated channel: ORA_DISK_3 hannel ORA_DISK_3: SID=58 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=272 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00014 to +DATA/T183/9DA0CE150DED43F7E0531200A8C0ECEC/DATAFILE/sysaux.266.1031338697 channel ORA_DISK_1: restoring datafile 00016 to +DATA/T183/9DA0CE150DED43F7E0531200A8C0ECEC/DATAFILE/users.259.1031338701 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_83uni5n7_1_1.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00013 to +DATA/T183/9DA0CE150DED43F7E0531200A8C0ECEC/DATAFILE/system.257.1031331865 channel ORA_DISK_2: restoring datafile 00015 to +DATA/T183/9DA0CE150DED43F7E0531200A8C0ECEC/DATAFILE/undotbs1.258.1031331863 channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_84uni68c_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_83uni5n7_1_1.bkp tag=TAG20200202T205625 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:52 channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_84uni68c_1_1.bkp tag=TAG20200202T205625 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:04:49 Finished restore at 03-FEB-20
Step #3: Recover the PDB.
The datafiles are back in ASM, so let’s move onto recovering them up to the point of failure:
RMAN> recover pluggable database t183_pdb2; Starting recover at 03-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 247 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_03/thread_1_seq_247.265.1031362673 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=245 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_88uni6ol_1_1.bkp channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=246 channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-02/2020-02-02_23:00/T183_8aunid8g_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-02/2020-02-02_20:44/T183_88uni6ol_1_1.bkp tag=TAG20200202T211542 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:46 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_03/thread_1_seq_245.280.1031389841 thread=1 sequence=245 channel default: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2020_02_03/thread_1_seq_245.280.1031389841 RECID=248 STAMP=1031389881 media recovery complete, elapsed time: 00:05:49 channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-02/2020-02-02_23:00/T183_8aunid8g_1_1.bkp tag=TAG20200202T230642 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:06:37 channel default: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2020_02_03/thread_1_seq_246.282.1031389851 RECID=249 STAMP=1031389890 Finished recover at 03-FEB-20
Step #4: Open the PDB.
RMAN> alter pluggable database t183_pdb2 open; Statement processed
Step #5: Resync the Recovery Catalog.
Operations have occurred which did not involve the Recovery catalog. So let’s go ahead and resync just for safety’s sake. Note, RMAN backups or typical RMAN commands like “show all;” perform a resync anyway. To perform a resync here, log out of RMAN then log back in again, connecting to the Recovery Catalog:
[oracle@orasvr02 2020-02-02]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Mon Feb 3 09:32:49 2020 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> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> resync catalog; starting full resync of recovery catalog full resync complete
Task #4c. Point In Time (Incomplete) Restore/Recovery of a CDB/non-CDB.
For this example, I’ll use the non-CDB T122 database. To ensure we’re taking the database back in time, let’s create a new table, perform the Point-In-Time (PIT) restore/recovery to before the table existed, then check to see if the table is still there. As the MEDIA user, let’s check the list of tables we own and create a new one, making a note of the date and time we did that:
SQL> select table_name from user_tables; TABLE_NAME ---------------------- GENRES MEDIA_TYPES FORMATS RECORDING_ARTISTS RELEASES TITLES COMPANIES COMMUNICATION_TYPES COMMUNICATIONS COMPANIES_EXT 10 rows selected. SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') "Time Right Now" from dual; Time Right Now ------------------ 03-FEB-20 15:01:14 SQL> create table TEST_TABLE (col1 number); Table created. SQL> select table_name from user_tables; TABLE_NAME ---------------------- GENRES MEDIA_TYPES FORMATS RECORDING_ARTISTS RELEASES TITLES COMPANIES COMMUNICATION_TYPES COMMUNICATIONS COMPANIES_EXT TEST_TABLE 11 rows selected.
Step #1: Assess the Situation.
In any PIT restore/recovery scenario, the most important issue is deciding the point to which you want to restore and recover the database. Incomplete restore and recoveries mean there will be data loss, so getting the restore and recovery point locked down is crucial. There are 4 options when defining the restore and recovery point:
| Option | Usage Details |
|---|---|
| Time Based | UNTIL TIME "TO_DATE('your-date-&-time','MM/DD/YY HH24:MI:SS')" |
| Time based recovery is not exact because Oracle resolves the time to a given SCN. | |
| SCN Based | UNTIL SCN your-scn |
| SCN based is more precise but remember to add 1 to your-scn to include that SCN in your recovery. | |
| Change Based | UNTIL SEQUENCE your-sequence THREAD your-thread |
| Changed based will recover up to the given archived redo log file sequence. Remember to add 1 to your-sequence to include that archived redo log file in your recovery. | |
| Restore Point Based | UNTIL RESTORE POINT your-restore-point |
| A restore point is really just a name given to a point in time. Often used with Flashback Database. Normal restore points will eventually be aged out of the database. Guaranteed restore points won't be, but Oracle will create Flashback Logs to support guaranteed restore points. Think tons of storage on busy systems! | |
We know the new table was created around 3PM on February 3rd 2020, so let’s restore and recover the database back to 2:30PM on February 2nd 2020.
Step #2: Shutdown and Mount the Database.
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 3 15:33:20 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: T122 (DBID=2185934179) RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database recovery catalog schema release 18.03.00.00. is newer than RMAN release RMAN> shutdown immediate; database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 2147483648 bytes Fixed Size 8622776 bytes Variable Size 603983176 bytes Database Buffers 1526726656 bytes Redo Buffers 8151040 bytes
Step #3: Restore the Database to a Given Point In Time.
We want to restore and recover the database to February 2nd at 2:30PM. RMAN will use the database backup closest to and before that point in time. In our case, it will be the backup taken on Saturday February 1st at 8PM:
RMAN> restore database until time "to_date('02/02/20 14:30:00','MM/DD/YY HH24:MI:SS')";
Starting restore at 03-FEB-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/T122/datafile/o1_mf_system_h398cz46_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/T122/datafile/o1_mf_undotbs1_h398d01w_.dbf
channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4hunfe1t_1_1.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to /u02/oradata/T122/datafile/o1_mf_sysaux_h398czd6_.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u02/oradata/T122/datafile/o1_mf_users_h398d0dl_.dbf
channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4gunfe1s_1_1.bkp
channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4hunfe1t_1_1.bkp tag=TAG20200201T200200
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:45
channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4gunfe1s_1_1.bkp tag=TAG20200201T200200
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:14:55
Finished restore at 03-FEB-20
Step #4: Recover the Database to a Given Point In Time.
This will use cause RMAN to restore and use archived redo log files to roll the database forward from the restored point (02/01/20 @ 8PM) to the required recovery point (02/02/20 @ 2:30PM):
RMAN> recover database until time "to_date('02/02/20 14:30:00','MM/DD/YY HH24:MI:SS')";
Starting recover at 03-FEB-20
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4iunfeh3_1_1.bkp
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-02-01/2020-02-01_23:00/T122_4kunfohr_1_1.bkp
channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-02-01/2020-02-01_20:00/T122_4iunfeh3_1_1.bkp tag=TAG20200201T201009
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_7_h3k77rxw_.arc thread=1 sequence=7
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_7_h3k77rxw_.arc RECID=237 STAMP=1031415711
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /nas/backups/T122/2020-02-02/2020-02-02_23:00/T122_4nunicsh_1_1.bkp
channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-02-01/2020-02-01_23:00/T122_4kunfohr_1_1.bkp tag=TAG20200201T230114
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:05:04
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_8_h3k77z0p_.arc thread=1 sequence=8
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_8_h3k77z0p_.arc RECID=238 STAMP=1031415745
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_2: reading from backup piece /nas/backups/T122/2020-02-02/2020-02-02_23:00/T122_4municsf_1_1.bkp
channel ORA_DISK_1: piece handle=/nas/backups/T122/2020-02-02/2020-02-02_23:00/T122_4nunicsh_1_1.bkp tag=TAG20200202T230029
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:15:08
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_9_h3k7kfmq_.arc thread=1 sequence=9
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_9_h3k7kfmq_.arc RECID=240 STAMP=1031416145
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_10_h3k7kb0y_.arc thread=1 sequence=10
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_10_h3k7kb0y_.arc RECID=239 STAMP=1031416139
channel ORA_DISK_2: piece handle=/nas/backups/T122/2020-02-02/2020-02-02_23:00/T122_4municsf_1_1.bkp tag=TAG20200202T230029
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:33:51
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_11_h3k8fp8r_.arc thread=1 sequence=11
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T122/archivelog/2020_02_03/o1_mf_1_11_h3k8fp8r_.arc RECID=241 STAMP=1031416977
media recovery complete, elapsed time: 00:14:19
Finished recover at 03-FEB-20
Step #5: Open the Database.
RMAN> alter database open resetlogs; Statement processed new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete
Finally, login as the MEDIA user and check the list of tables:
SQL> select table_name from user_tables; TABLE_NAME ---------------------- GENRES MEDIA_TYPES FORMATS RECORDING_ARTISTS RELEASES TITLES COMPANIES COMMUNICATION_TYPES COMMUNICATIONS COMPANIES_EXT 10 rows selected.
TEST_TABLE is no longer present in the database. Yes!
Task #4d. Point In Time (Incomplete) Restore/Recovery of a PDB.
For this example we’ll use the PDB, T183_PDB1 within the CDB T183. This time we’ll restore and recover to a given SCN. To help with this task, I have created a table in my own account in T183_PDB1, containing rows created at different times and hence different SCNs. The pseudo column ORA_ROWSCN gives us the SCN associated with each row:
SQL> show con_name CON_NAME --------- T183_PDB1 SQL> show user USER is "SFRANCIS" SQL> select ORA_ROWSCN,RANK,TITLE,YEAR from best_numan_albums order by ORA_ROWSCN; ORA_ROWSCN RANK TITLE YEAR ---------- ---------- ------------------------------ ---- 10134233 1 The Pleasure Principle 1979 10134311 2 Telekon 1980 10134770 3 Exile 1997 10134965 4 Pure 2000 10135391 5 Dance 1981 5 rows selected.
Step #1: Assess the Situation.
In any PIT restore/recovery scenario, the most important issue is to assess the point to which you wish to recover. An SCN PIT recovery will recover up to, but not including, the SCN you specify. So in our case, if we only want to recover the first 3 rows of this table, we need to specify an SCN of 10134770+1.
Step #2: Close the PDB.
[oracle@orasvr02 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Tue Feb 4 13:03:51 2020 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> alter pluggable database T183_PDB1 close; using target database control file instead of recovery catalog Statement processed
Step #3: Restore the PDB to the Given SCN.
RMAN> restore pluggable database T183_PDB1 until SCN 10134771; Starting restore at 04-FEB-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=295 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=292 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=288 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00018 to +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/sysaux.266.1031477343 channel ORA_DISK_1: restoring datafile 00020 to +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/users.258.1031478153 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-04/2020-02-04_09:51/T183_8lunm7vb_1_1.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00017 to +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/system.259.1031477337 channel ORA_DISK_2: restoring datafile 00019 to +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/undotbs1.257.1031477335 channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-04/2020-02-04_09:51/T183_8nunm8fi_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-04/2020-02-04_09:51/T183_8nunm8fi_1_1.bkp tag=TAG20200204T095900 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:04:18 channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-04/2020-02-04_09:51/T183_8lunm7vb_1_1.bkp tag=TAG20200204T095900 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:35 Finished restore at 04-FEB-20
Step #4: Recover the PDB to the Given SCN.
RMAN> recover pluggable database T183_PDB1 until SCN 10134771; Starting recover at 04-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 253 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_04/thread_1_seq_253.283.1031480207 archived log for thread 1 with sequence 254 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_04/thread_1_seq_254.289.1031486659 archived log for thread 1 with sequence 255 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_04/thread_1_seq_255.285.1031487353 archived log for thread 1 with sequence 256 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_04/thread_1_seq_256.287.1031489371 media recovery complete, elapsed time: 00:01:51 Finished recover at 04-FEB-20
Step #5: Open the PDB.
RMAN> alter pluggable database T183_PDB1 open resetlogs; Statement processed
Let’s check to see if the result is what we expected:
SQL> select ORA_ROWSCN,RANK,TITLE,YEAR from best_numan_albums order by ORA_ROWSCN; ORA_ROWSCN RANK TITLE YEAR ---------- ---------- ------------------------------ ---- 10134233 1 The Pleasure Principle 1979 10134311 2 Telekon 1980 10134770 3 Exile 1997 3 rows selected.
Albums 3 and 4 are now gone which is a pity becasue they’re both excellent. 🎵
Step #6: Resync the Recovery Catalog.
Operations have occurred which did not involve the Recovery catalog. So let’s go ahead and resync just for safety’s sake. Note, RMAN backups or typical RMAN commands like “show all;” perform a resync anyway. To perform a resync here, log out of RMAN then log back in again, connecting to the Recovery Catalog:
[oracle@orasvr02 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Tue Feb 4 13:42:20 2020 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> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> resync catalog; starting full resync of recovery catalog full resync complete
Task #5: More Advanced Uses of RMAN.
For this section, we’ll cover some more complicated RMAN recovery scenarios which will really help demonstrate how RMAN works. This is what we’ll be doing (click the link you need):
Task #5a. Point of Failure (Complete) PDB Data File Restore/Recovery.
Task #5b. Point In Time Database Restore/Recovery to a New Server (ASM to File System).
Task #5c. Point In Time Database Restore/Recovery to a New Server (File System to ASM). (coming soon)
Task #5d. Tablespace Point In Time Restore/Recovery. (coming soon)
Task #5e. Table Restore/Recovery. (coming soon)
Task #5f. Active Duplication. (coming soon)
Task #5a. Point of Failure (Complete) PDB Data File Restore/Recovery.
For this example we’ll use the T183_PDB1 PDB within the T183 CDB running on orasvr02. This server runs an ASM instance which won’t allow me to delete a datafile while the PDB is open. Therefore, to simulate a problem with the PDB, I will close the PDB, delete the USERS tablespace datafile, then attempt to re-open the PDB. Bad things will happen as a result.
Let’s just check the location of the FRA and the backup files:
SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------ db_recovery_file_dest string +RECO db_recovery_file_dest_size big integer 10G recovery_parallelism integer 0 remote_recovery_file_dest string [oracle@orasvr02 2020-02-01]$ pwd /nas/backups/T183/2020-02-01 [oracle@orasvr02 2020-02-01]$ ls -l drwxrwxrwx 2 oracle oinstall 4096 Feb 1 20:38 2020-02-01_20:00 <-- Full CDB Backup Files drwxrwxrwx 2 oracle oinstall 4096 Feb 1 23:10 2020-02-01_23:00 <-- Archived Redo Log Backup Files
Step #1: Assess the Situation.
Before you do anything, find out what’s broken and what’s still left of the database. This will determine your approach to restoring and recovering the database and will prime you for what to expect from RMAN’s output.
SQL> alter pluggable database t183_pdb1 open; alter pluggable database t183_pdb1 open * ERROR at line 1: ORA-01157: cannot identify/lock data file 12 - see DBWR trace file ORA-01110: data file 12: '+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/users.257.1025001295'
It looks like only a single datafile is missing, but let’s check. The PDB should have these datafiles:
FILE_ID FILE_NAME ------- -----------------------------------------------------------------------------11 +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/undotbs1.266.102460411110 +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/sysaux.259.10246041119 +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/system.267.102460411112 +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/users.257.1025001295
Let’s verify that in ASM using ASMCMD:
ASMCMD> pwd +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE FEB 02 13:00:00 Y SYSAUX.259.1024604111 DATAFILE UNPROT COARSE FEB 02 13:00:00 Y SYSTEM.267.1024604111 DATAFILE UNPROT COARSE FEB 02 13:00:00 Y UNDOTBS1.266.1024604111
We know the CDB instance is still up and running and not reporting any further errors, so the problem does appear to be a single missing PDB datafile. At this point we have a choice concerning how to restore and recover the PDB. The end result will be the same (PDB back up and running), but the potential time it takes could be very different depending on how many datafiles are impacted. The choices are:
| Restore/Recovery Option | Details |
|---|---|
| Full PDB Restore/Recovery | Choose this option if all or most of the database datafiles are impacted |
| Tablespace Restore/Recovery | Choose this option if all or most of a tablespace(s) datafiles are impacted |
| Datafile Restore/Recovery | Choose this option if relatively few database datafiles are impacted |
In other words, if the database had 100 data files and only one was impacted, it would be better to perform a data file restore/recovery. Similarly, if a database had 100 data files and a tablespace was impacted which had 10 data files, a tablespace restore/recovery would be the way to go. In our case, a datafile or tablespace restore/recovery are equivalent because the tablespace (USERS) only has one datafile (users.257.1025001295). Let’s perform a data file restore and recovery.
Step #2: Close the PDB.
[oracle@orasvr02 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Sun Feb 2 13:55:59 2020 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> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> alter pluggable database t183_pdb1 close; starting full resync of recovery catalog full resync complete RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 02/02/2020 14:16:38 ORA-65020: pluggable database T183_PDB1 already closed
We already knew the PDB was closed, so RMAN at least confirms this. No foul.
Step #3: Restore the Missing Data File.
Oddly enough, I did attempt to restore the whole PDB which failed consistently. RMAN didn’t bother to explain why (typical RMAN), so I stuck with Plan A and restored just the missing data file:
RMAN> restore datafile 12; Starting restore at 02-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00012 to +DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/users.257.1025001295 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-01/2020-02-01_20:00/T183_74unfet8_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-01/2020-02-01_20:00/T183_74unfet8_1_1.bkp tag=TAG20200201T201458 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:49 Finished restore at 02-FEB-20 starting full resync of recovery catalog full resync complete
Step #4: Recover the Restored Data File.
With the missing data file back in play, time to recover it so it’s consistent with the rest of the database:
RMAN> recover datafile 12; Starting recover at 02-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 232 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_232.272.1031271249 archived log for thread 1 with sequence 233 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_233.271.1031275935 archived log for thread 1 with sequence 234 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_234.273.1031294207 archived log for thread 1 with sequence 235 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_235.274.1031300027 archived log for thread 1 with sequence 236 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_236.263.1031305429 archived log for thread 1 with sequence 237 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_237.262.1031310847 archived log for thread 1 with sequence 238 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_238.287.1031316259 archived log for thread 1 with sequence 239 is already on disk as file +RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_239.286.1031322865 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=229 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-01/2020-02-01_20:00/T183_7aunfg6b_1_1.bkp channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=230 channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-01/2020-02-01_23:00/T183_7cunfp1o_1_1.bkp channel ORA_DISK_3: starting archived log restore to default destination channel ORA_DISK_3: restoring archived log archived log thread=1 sequence=231 channel ORA_DISK_3: reading from backup piece /nas/backups/T183/2020-02-01/2020-02-01_23:00/T183_7dunfp1t_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-01/2020-02-01_20:00/T183_7aunfg6b_1_1.bkp tag=TAG20200201T203819 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:46 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_229.279.1031326419 thread=1 sequence=229 channel default: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_229.279.1031326419 RECID=237 STAMP=1031326467 channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-01/2020-02-01_23:00/T183_7cunfp1o_1_1.bkp tag=TAG20200201T230902 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:02:46 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_230.259.1031326437 thread=1 sequence=230 channel default: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_230.259.1031326437 RECID=239 STAMP=1031326549 channel ORA_DISK_3: piece handle=/nas/backups/T183/2020-02-01/2020-02-01_23:00/T183_7dunfp1t_1_1.bkp tag=TAG20200201T230902 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:04:14 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_231.283.1031326451 thread=1 sequence=231 channel default: deleting archived log(s) archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_231.283.1031326451 RECID=238 STAMP=1031326544 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_232.272.1031271249 thread=1 sequence=232 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_233.271.1031275935 thread=1 sequence=233 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_234.273.1031294207 thread=1 sequence=234 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_235.274.1031300027 thread=1 sequence=235 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_236.263.1031305429 thread=1 sequence=236 archived log file name=+RECO/T183/ARCHIVELOG/2020_02_02/thread_1_seq_237.262.1031310847 thread=1 sequence=237 media recovery complete, elapsed time: 00:10:00 Finished recover at 02-FEB-20
Step #5: Open the PDB.
RMAN> alter pluggable database t183_pdb1 open; Statement processed starting full resync of recovery catalog full resync complete
Let’s just check everything looks OK – not that I’m paranoid of course:
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
--------------- ----------
PDB$SEED READ ONLY
T183_PDB1 READ WRITE
SQL> select name, status from v$datafile
where con_id = (select con_id from v$pdbs where name = 'T183_PDB1');
NAME STATUS
-------------------------------------------------------------------------------- ------
+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/system.267.1024604111 SYSTEM
+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/sysaux.259.1024604111 ONLINE
+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/undotbs1.266.1024604111 ONLINE
+DATA/T183/9796845BE029589CE0531200A8C01B41/DATAFILE/users.257.1031324843 ONLINE
Task #5b. Point In Time Database Restore/Recovery to a New Server (ASM to File System).
Restoring a database backup to a different server and recovering it to a previous point in time is something which happens quite frequently. Developers often want to develop or test against a copy of production data. As we all know, once you restore a backup of a production database, the data magically becomes non-production and any attempt the DBA makes to scramble, mask or encrypt it is just wasting everyone’s time. 😒 Moving on. The real interest in this task though is changing the storage mechanism from the source database’s ASM to the destination database’s use of regular Linux file system storage. Not a simple as you might think. Here’s a summary of what we’ll be doing:
| Target | Server | Database | Storage |
|---|---|---|---|
| Source | orasvr02 | T183 (18c R3 CDB) | ASM |
| (Incl. PDB$SEED, T183_PDB1) | (+DATA, +RECO, +REDO) | ||
| Destination | orasvr01 | D183 (18c R3 CDB) | File System |
| (Incl. PDB$SEED, T183_PDB1) | (/u03/oradata, /u07/oradata) | ||
A full database backup of T183 was taken around midday on 02/12/20 with archived redo log file backups occurring daily at 11PM. We will restore and recover this database to 02/13/20 at 3:00AM. For extra fun and intrigue, let’s not cheat by copying the SPFILE and control file from the source database server. By not doing that we actually run into a couple of problems, the solution to which is a neat trick that’s well worth knowing. All the necessary database and archived redo log file backups are stored in NFS storage, mounted to both servers. There are 12 steps, so let’s crack on.
Step #1: Restore the SPFILE.
We know we’re using control file autobackup, so checking the log file of the full database backup which occurred at 12PM on 02/12/20 we see this:
... Starting Control File and SPFILE Autobackup at 12-FEB-20 piece handle=+RECO/T183/AUTOBACKUP/2020_02_12/s_1032179709.272.1032179735 comment=NONE Finished Control File and SPFILE Autobackup at 12-FEB-20 ...
So we need to get hold of the file, s_1032179709.272.1032179735.
On orasvr02, we log into the ASM instance using ASMCMD and copy the autobackup file containing the SPFILE (and control file) to NFS storage:
ASMCMD> cd +RECO/T183/AUTOBACKUP/2020_02_12/ ASMCMD> ls -l Type Redund Striped Time Sys Name AUTOBACKUP UNPROT COARSE FEB 12 12:00:00 Y s_1032179709.272.1032179735 AUTOBACKUP UNPROT COARSE FEB 12 23:00:00 Y s_1032217930.256.1032217959 ASMCMD> cp s_1032179709.272.1032179735 /nas/backups/T183/tmp copying +RECO/T183/AUTOBACKUP/2020_02_12/s_1032179709.272.1032179735 -> /nas/backups/T183/tmp/s_1032179709.272.1032179735
From orasvr01, let’s check the file is there:
[oracle@orasvr01 tmp]$ pwd /nas/backups/T183/tmp [oracle@orasvr01 tmp]$ ls -l -rw-r----- 1 grid oinstall 1146880 Feb 13 10:15 s_1032179709.272.1032179735
We have 2 problems. First, the file has the wrong ownership for our purposes and second, the file name is in the wrong format. Using the root account fixes the first issue, but we need to address the file naming convention so RMAN will be able to read it. RMAN autobackups are written using the %F format specification. From the Oracle Documentation:
| Syntax Element | Description |
|---|---|
| %F | Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where: |
| IIIIIIIIII stands for the DBID. The DBID is printed in decimal so that it can be easily associated with the target database. | |
| YYYYMMDD is a time stamp in the Gregorian calendar of the day the backup is generated | |
| QQ is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256) | |
| Note: %F is valid only in the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT command. | |
We know from using RMAN the DBID of T183 is 2832597398. The date element is obviously 20200212. We’ll just use 00 for the QQ component:
[root@orasvr01 ~]# cd /nas/backups/T183/tmp [root@orasvr01 tmp]# ls -l -rw-r----- 1 grid oinstall 1146880 Feb 13 10:15 s_1032179709.272.1032179735 [root@orasvr01 tmp]# chown oracle:oinstall * [root@orasvr01 tmp]# mv s_1032179709.272.1032179735 c-2832597398-20200212-00 [root@orasvr01 tmp]# ls -l -rw-r----- 1 oracle oinstall 1146880 Feb 13 10:15 c-2832597398-20200212-00
Next, add an entry for T183 in the /etc/oratab file on orasvr01:
T183:/u01/app/oracle/product/18.3.0/dbhome_1:N
Set your Oracle environment on orasvr01 then use RMAN to restore the SPFILE to a PFILE (you will need to edit it later):
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Thu Feb 13 10:19:58 2020 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set DBID = 2832597398; executing command: SET DBID RMAN> startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/18.3.0/dbhome_1/dbs/initT183.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073740616 bytes Fixed Size 8665928 bytes Variable Size 281018368 bytes Database Buffers 775946240 bytes Redo Buffers 8110080 bytes RMAN> set controlfile autobackup format for device type disk to '/nas/backups/T183/tmp/%F'; executing command: SET CONTROLFILE AUTOBACKUP FORMAT RMAN> restore spfile to pfile '?/dbs/initT183.ora' from autobackup; Starting restore at 13-FEB-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: looking for AUTOBACKUP on day: 20200213 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20200212 channel ORA_DISK_1: AUTOBACKUP found: /nas/backups/T183/tmp/c-2832597398-20200212-00 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /nas/backups/T183/tmp/c-2832597398-20200212-00 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 13-FEB-20 RMAN> shutdown abort Oracle instance shut down
Let’s check to see if we have the PFILE in $ORACLE_HOME/dbs:
[oracle@orasvr01 dbs]$ pwd
/u01/app/oracle/product/18.3.0/dbhome_1/dbs
[oracle@orasvr01 dbs]$ ls -l
-rw-rw---- 1 oracle oinstall 1544 Feb 13 10:21 hc_T183.dat
-rw-r--r-- 1 oracle oinstall 1174 Feb 13 10:21 initT183.ora
Next, edit the PFILE to reference orasvr01 directory paths and make any necessary changes to the memory parameters. At a minimum, check these entries:
T183.__oracle_base *.audit_file_dest *.control_files *.db_create_file_dest *.db_recovery_file_dest *.db_create_online_log_dest_1
Here is the restored PFILE:
T183._data_transfer_cache_size=0 T183.__db_cache_size=1409286144 T183.__inmemory_ext_roarea=0 T183.__inmemory_ext_rwarea=0 T183.__java_pool_size=16777216 T183.__large_pool_size=33554432 T183.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment T183.__pga_aggregate_target=1694498816 T183.__sga_target=2147483648 T183.__shared_io_pool_size=117440512 T183.__shared_pool_size=553648128 T183.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/T183/adump' *.audit_trail='db' *.compatible='18.0.0' *.control_files='+DATA/T183/CONTROLFILE/current.265.1024601175','+RECO/T183/CONTROLFILE/current.260.1024601175' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_create_online_log_dest_1='+REDO' *.db_domain='mynet.com' *.db_name='T183' *.db_recovery_file_dest='+RECO' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=T183XDB)' *.enable_pluggable_database=true *.local_listener='' *.log_archive_format='%t%s_%r.arc' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=1607m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2048m *.undo_tablespace='UNDOTBS1'
Here are the edits I made on orasvr01 in preparation for the restore of T183:
[oracle@orasvr01 ~]$ cd $ORACLE_BASE/admin [oracle@orasvr01 admin]$ ls -l drwxr-x--- 7 oracle oinstall 4096 Nov 15 18:49 T122 [oracle@orasvr01 admin]$ mkdir -p ./T183/adump [oracle@orasvr01 admin]$ chmod -R 750 ./T183 [oracle@orasvr01 admin]$ ls -l drwxr-x--- 7 oracle oinstall 4096 Nov 15 18:49 T122 drwxr-x--- 3 oracle oinstall 4096 Feb 12 17:44 T183 [oracle@orasvr01 ~]$ cd /u03/oradata [oracle@orasvr01 oradata]$ mkdir T183;chmod 750 T183 [oracle@orasvr01 oradata]$ ls -l drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 T183 [oracle@orasvr01 oradata]$ cd T183 [oracle@orasvr01 T183]$ mkdir controlfile datafile onlinelog [oracle@orasvr01 T183]$ chmod 750 * [oracle@orasvr01 T183]$ ls -l drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 controlfile drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 datafile drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 onlinelog [oracle@orasvr01 ~]$ cd /u07/oradata/fast_recovery_area [oracle@orasvr01 fast_recovery_area]$ mkdir T183;chmod 750 T183 [oracle@orasvr01 fast_recovery_area]$ cd T183 [oracle@orasvr01 T183]$ mkdir archivelog autobackup backupset controlfile onlinelog [oracle@orasvr01 T183]$ ls -l drwxr-xr-x 2 oracle oinstall 4096 Feb 12 18:14 archivelog drwxr-xr-x 2 oracle oinstall 4096 Feb 12 18:14 autobackup drwxr-xr-x 2 oracle oinstall 4096 Feb 12 18:14 backupset drwxr-xr-x 2 oracle oinstall 4096 Feb 12 18:14 controlfile drwxr-xr-x 2 oracle oinstall 4096 Feb 12 18:14 onlinelog
Here’s the edited version of the PFILE:
T183._data_transfer_cache_size=0 T183.__db_cache_size=1409286144 T183.__inmemory_ext_roarea=0 T183.__inmemory_ext_rwarea=0 T183.__java_pool_size=16777216 T183.__large_pool_size=33554432 T183.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment T183.__pga_aggregate_target=1694498816 T183.__sga_target=2147483648 T183.__shared_io_pool_size=117440512 T183.__shared_pool_size=553648128 T183.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/T183/adump' *.audit_trail='db' *.compatible='18.0.0' *.control_files='/u03/oradata/T183/controlfile/T183_ctrl_1.ctl','/u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl' *.db_block_size=8192 *.db_create_file_dest=/u03/oradata *.db_create_online_log_dest_1=/u03/oradata *.db_create_online_log_dest_2=/u07/oradata/fast_recovery_area *.db_domain='mynet.com' *.db_name='T183' *.db_recovery_file_dest=/u07/oradata/fast_recovery_area *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=T183XDB)' *.enable_pluggable_database=true *.local_listener='' *.log_archive_format='%t%s_%r.arc' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=1607m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2048m *.undo_tablespace='UNDOTBS1'
Step #2: Restore the Control File.
Re-start the instance using the editing PFILE and restore the control file from autobackup:
RMAN> startup force nomount pfile='?/dbs/initT183.ora' Oracle instance started Total System Global Area 2147481064 bytes Fixed Size 8898024 bytes Variable Size 603979776 bytes Database Buffers 1526726656 bytes Redo Buffers 7876608 bytes RMAN> restore controlfile from autobackup; Starting restore at 13-FEB-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK recovery area destination: /u07/oradata/fast_recovery_area database name (or database unique name) used for search: T183 channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20200213 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20200212 channel ORA_DISK_1: AUTOBACKUP found: /nas/backups/T183/tmp/c-2832597398-20200212-00 channel ORA_DISK_1: restoring control file from AUTOBACKUP /nas/backups/T183/tmp/c-2832597398-20200212-00 channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u03/oradata/T183/controlfile/T183_ctrl_1.ctl output file name=/u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl Finished restore at 13-FEB-20
Step #3: Mount the Database.
The control files should have been restored to the locations specified in the PFILE. Let’s check:
[oracle@orasvr01 dbs]$ ls -l /u03/oradata/T183/controlfile/T183_ctrl_1.ctl -rw-r----- 1 oracle oinstall 18825216 Feb 13 10:27 /u03/oradata/T183/controlfile/T183_ctrl_1.ctl [oracle@orasvr01 dbs]$ ls -l /u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl -rw-r----- 1 oracle oinstall 18825216 Feb 13 10:27 /u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl
Now that the control files are restored, we can mount the database and find out the names of all the other files we will need to restore:
RMAN> alter database mount; RMAN> report schema; Starting implicit crosscheck backup at 13-FEB-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=260 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=25 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=261 device type=DISK Crosschecked 96 objects Crosschecked 26 objects Finished implicit crosscheck backup at 13-FEB-20 Starting implicit crosscheck copy at 13-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 Finished implicit crosscheck copy at 13-FEB-20 searching for all files in the recovery area cataloging files… no files cataloged RMAN-06139: warning: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name T183 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- -- ---- ------------------------------------------------------------- 1 0 SYSTEM *** +DATA/T183/DATAFILE/system.270.1024601039 3 0 SYSAUX *** +DATA/T183/DATAFILE/sysaux.261.1024601085 4 0 UNDOTBS1 *** +DATA/T183/DATAFILE/undotbs1.274.1024601109 5 0 PDB$SEED:SYSTEM *** +DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.271.1024601571 6 0 PDB$SEED:SYSAUX *** +DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.272.1024601571 7 0 USERS *** +DATA/T183/DATAFILE/users.269.1024601111 8 0 PDB$SEED:UNDOTBS1 *** +DATA/T183/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.273.1024601571 17 0 T183_PDB1:SYSTEM *** +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/system.259.1031477337 18 0 T183_PDB1:SYSAUX *** +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/sysaux.266.1031477343 19 0 T183_PDB1:UNDOTBS1 *** +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/undotbs1.257.1031477335 20 0 T183_PDB1:USERS *** +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/DATAFILE/users.258.1031478153 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- --------------------------------------------------------- 1 20 TEMP 32767 +DATA/T183/TEMPFILE/temp.275.1024601211 2 62 PDB$SEED:TEMP 32767 +DATA/T183/9795EEED00203DFFE0531200A8C06D7B/TEMPFILE/temp.268.1024601603 3 62 T183_PDB1:TEMP 32767 +DATA/T183/9DC2AE83F20B40ACE0531200A8C05C14/TEMPFILE/temp.267.1031477735 RMAN> select member from v$logfile; MEMBER ------------------------------------------- +DATA/T183/ONLINELOG/group_3.262.1024601181 +RECO/T183/ONLINELOG/group_3.266.1024601187 +DATA/T183/ONLINELOG/group_2.263.1024601181 +RECO/T183/ONLINELOG/group_2.261.1024601187 +DATA/T183/ONLINELOG/group_1.264.1024601181 +RECO/T183/ONLINELOG/group_1.267.1024601187
Note, the TEMP file for PDB$SEED is listed in a different GUID directory from its other 3 files.
Using this data, we need to do a couple of things. First, pre-create some additional directories to separate out the files belonging to the SEED and T183_PDB1 PDBs. Second, create a script which will restore all the data files and create the online redo log files in the correct locations.
[oracle@orasvr01 T183]$ pwd /u03/oradata/T183 [oracle@orasvr01 T183]$ mkdir -p ./64A52F53A7693286E053CDA9E80AED76/datafile [oracle@orasvr01 T183]$ mkdir -p ./9DC2AE83F20B40ACE0531200A8C05C14/datafile [oracle@orasvr01 T183]$ chmod -R 750 ./64A52F53A7693286E053CDA9E80AED76/datafile [oracle@orasvr01 T183]$ chmod -R 750 ./9DC2AE83F20B40ACE0531200A8C05C14/datafile [oracle@orasvr01 T183]$ chmod 750 * [oracle@orasvr01 T183]$ ls -l drwxr-x--- 3 oracle oinstall 4096 Feb 12 20:08 64A52F53A7693286E053CDA9E80AED76 drwxr-x--- 3 oracle oinstall 4096 Feb 12 20:08 9DC2AE83F20B40ACE0531200A8C05C14 drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:25 controlfile drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 datafile drwxr-x--- 2 oracle oinstall 4096 Feb 12 18:10 onlinelog
Here’s the RMAN script (called restore_recover.rman) which will rename all the files to their new locations. The UNTIL TIME clause is set for the block, then the restore will happen (except for the online redo log files), then the database will switch to using the files in their new locations. Finally, the recovery will roll the database forward from the backup time of 02/12/20 at 12:00PM to the recovery time of 02/13/20 at 3:00AM:
run
{
set newname for datafile 1 to '/u03/oradata/T183/datafile/%U.dbf';
set newname for datafile 3 to '/u03/oradata/T183/datafile/%U.dbf';
set newname for datafile 4 to '/u03/oradata/T183/datafile/%U.dbf';
set newname for datafile 7 to '/u03/oradata/T183/datafile/%U.dbf';
set newname for datafile 5 to '/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/%U.dbf';
set newname for datafile 6 to '/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/%U.dbf';
set newname for datafile 8 to '/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/%U.dbf';
set newname for datafile 17 to '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/%U.dbf';
set newname for datafile 18 to '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/%U.dbf';
set newname for datafile 19 to '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/%U.dbf';
set newname for datafile 20 to '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/%U.dbf';
alter database rename file '+DATA/T183/ONLINELOG/group_1.264.1024601181' to
'/u03/oradata/T183/onlinelog/redo_g1m1.log';
alter database rename file '+RECO/T183/ONLINELOG/group_1.267.1024601187' to
'/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log';
alter database rename file '+DATA/T183/ONLINELOG/group_2.263.1024601181' to
'/u03/oradata/T183/onlinelog/redo_g2m1.log';
alter database rename file '+RECO/T183/ONLINELOG/group_2.261.1024601187' to
'/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log';
alter database rename file '+DATA/T183/ONLINELOG/group_3.262.1024601181' to
'/u03/oradata/T183/onlinelog/redo_g3m1.log';
alter database rename file '+RECO/T183/ONLINELOG/group_3.266.1024601187' to
'/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log';
set until time "to_date('02/13/20 03:00:00','MM/DD/YY HH24:MI:SS')";
restore database;
switch datafile all;
recover database;
}
Step #4: Restore and Recover the Database.
Let’s run the RMAN script and see how far we get (spoiler alert – it will croak – pop quiz – do you know why yet?) 😃:
RMAN> @restore_recover.rman executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Statement processed Statement processed Statement processed Statement processed Statement processed Statement processed executing command: SET until clause Starting restore at 13-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00017 to /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf channel ORA_DISK_1: restoring datafile 00018 to /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a9uobipk_1_1.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf channel ORA_DISK_2: restoring datafile 00004 to /u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a8uobip7_1_1.bkp channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00003 to /u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf channel ORA_DISK_3: restoring datafile 00007 to /u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf channel ORA_DISK_3: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a7uobior_1_1.bkp channel ORA_DISK_4: starting datafile backup set restore channel ORA_DISK_4: specifying datafile(s) to restore from backup set channel ORA_DISK_4: restoring datafile 00006 to /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSAUX_FNO-6.dbf channel ORA_DISK_4: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_abuobjdi_1_1.bkp channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a8uobip7_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:22:17 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00019 to /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf channel ORA_DISK_2: restoring datafile 00020 to /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aauobjc2_1_1.bkp channel ORA_DISK_3: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a7uobior_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:22:17 channel ORA_DISK_3: starting datafile backup set restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set channel ORA_DISK_3: restoring datafile 00005 to /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSTEM_FNO-5.dbf channel ORA_DISK_3: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_acuobjir_1_1.bkp channel ORA_DISK_4: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_abuobjdi_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_4: restored backup piece 1 channel ORA_DISK_4: restore complete, elapsed time: 00:22:17 channel ORA_DISK_4: starting datafile backup set restore channel ORA_DISK_4: specifying datafile(s) to restore from backup set channel ORA_DISK_4: restoring datafile 00008 to /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-UNDOTBS1_FNO-8.dbf channel ORA_DISK_4: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aduobjkq_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_a9uobipk_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:28:04 channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aauobjc2_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:05:47 channel ORA_DISK_3: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_acuobjir_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:05:46 channel ORA_DISK_4: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aduobjkq_1_1.bkp tag=TAG20200212T121249 channel ORA_DISK_4: restored backup piece 1 channel ORA_DISK_4: restore complete, elapsed time: 00:05:46 Finished restore at 13-FEB-20 datafile 1 switched to datafile copy input datafile copy RECID=15 STAMP=1032261425 file name=/u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf datafile 3 switched to datafile copy input datafile copy RECID=16 STAMP=1032261429 file name=/u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf datafile 4 switched to datafile copy input datafile copy RECID=17 STAMP=1032261433 file name=/u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf datafile 5 switched to datafile copy input datafile copy RECID=18 STAMP=1032261437 file name=/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSTEM_FNO-5.dbf datafile 6 switched to datafile copy input datafile copy RECID=19 STAMP=1032261441 file name=/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSAUX_FNO-6.dbf datafile 7 switched to datafile copy input datafile copy RECID=20 STAMP=1032261444 file name=/u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf datafile 8 switched to datafile copy input datafile copy RECID=21 STAMP=1032261448 file name=/u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-UNDOTBS1_FNO-8.dbf datafile 17 switched to datafile copy input datafile copy RECID=22 STAMP=1032261452 file name=/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf datafile 18 switched to datafile copy input datafile copy RECID=23 STAMP=1032261455 file name=/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf datafile 19 switched to datafile copy input datafile copy RECID=24 STAMP=1032261460 file name=/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf datafile 20 switched to datafile copy input datafile copy RECID=25 STAMP=1032261463 file name=/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf Starting recover at 13-FEB-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=297 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aeuobjsb_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aeuobjsb_1_1.bkp tag=TAG20200212T123236 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_297_h4c17cw5_.arc thread=1 sequence=297 channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aeuobjsb_1_1.bkp channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_12:02/T183_aeuobjsb_1_1.bkp tag=TAG20200212T123236 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_297_h4c17cw5_.arc thread=1 sequence=297 channel default: deleting archived log(s) archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_297_h4c17cw5_.arc RECID=300 STAMP=1032261517 unable to find archived log archived log thread=1 sequence=298 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/13/2020 11:19:16 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 298 and starting SCN of 11183920 RMAN> **end-of-file**
The restore was completed successfully, but the roll forward recovery failed because RMAN could not find archived redo log file sequence 298. It’s not present in any of the backup piece files the control file knows about, neither is it in the database’s FRA. The reason is the control file we used to mount the database was autobacked up on 02/12/20 at around midday and the next archived redo log file backup didn’t happen until 11:00PM the same day. Hence the control file doesn’t know about this backup.The neat trick here is to simply tell the control file about those backup piece files using the CATALOG command. First, let’s grab the names of the backup piece files from the log file of the archived redo log file backup:
[oracle@orasvr01 2020-02-12]$ fgrep 'piece handle=' T183_2020-02-12_23:00_LOGS_rman.log piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_aguocp3d_1_1.bkp tag=TAG20200212T230804 comment=NONE piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_ahuocp3i_1_1.bkp tag=TAG20200212T230804 comment=NONE piece handle=+RECO/T183/AUTOBACKUP/2020_02_12/s_1032217930.256.1032217959 comment=NONE
Next, we’ll add a reference to these backups to the control file:
RMAN> catalog backuppiece '/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_aguocp3d_1_1.bkp'; cataloged backup piece backup piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_aguocp3d_1_1.bkp RECID=334 STAMP=1032272615 RMAN> catalog backuppiece '/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_ahuocp3i_1_1.bkp'; cataloged backup piece backup piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_ahuocp3i_1_1.bkp RECID=335 STAMP=1032272640
Now we can re-start the recovery process which rather cleverly picks up where it failed:
RMAN> run
2> {
3> set until time "to_date('02/13/20 03:00:00','MM/DD/YY HH24:MI:SS')";
4> recover database;
5> }
executing command: SET until clause
Starting recover at 13-FEB-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=298
channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_aguocp3d_1_1.bkp
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=299
channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_ahuocp3i_1_1.bkp
channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_ahuocp3i_1_1.bkp tag=TAG20200212T230804
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-12/2020-02-12_23:00/T183_aguocp3d_1_1.bkp tag=TAG20200212T230804
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:34
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_298_h4cdz7sb_.arc thread=1 sequence=298
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_298_h4cdz7sb_.arc RECID=302 STAMP=1032273625
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_299_h4cdz9mb_.arc thread=1 sequence=299
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_299_h4cdz9mb_.arc RECID=301 STAMP=1032273556
unable to find archived log
archived log thread=1 sequence=300
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2020 15:06:12
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 300 and starting SCN of 11240718
Oh no! What now? RMAN is now complaining about not having archived redo log file sequence #300. Why couldn’t RMAN tell us all the backups it would need up front? Well, there’s actually a way to do that. See the next section, Task #6 Additional RMAN Features. Let’s run a quick archived redo log backup of T183, then catalog the backup piece files that creates.
RMAN> catalog backuppiece '/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_ajuoehsm_1_1.bkp'; cataloged backup piece backup piece handle=/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_ajuoehsm_1_1.bkp RECID=336 STAMP=1032276720 RMAN> catalog backuppiece '/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_akuoehsr_1_1.bkp'; cataloged backup piece backup piece handle=/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_akuoehsr_1_1.bkp RECID=337 STAMP=1032276750
Now RMAN should have everything it needs and the restore should run to completion. Again, notice how RMAN is smart enough to pick up from where it failed:
RMAN> run
2> {
3> set until time "to_date('02/13/20 03:00:00','MM/DD/YY HH24:MI:SS')";
4> recover database;
5> }
executing command: SET until clause
Starting recover at 13-FEB-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=300
channel ORA_DISK_1: reading from backup piece /nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_ajuoehsm_1_1.bkp
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=301
channel ORA_DISK_2: reading from backup piece /nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_akuoehsr_1_1.bkp
channel ORA_DISK_1: piece handle=/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_ajuoehsm_1_1.bkp tag=TAG20200213T151726
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:06
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_300_h4cj5wyw_.arc thread=1 sequence=300
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_300_h4cj5wyw_.arc RECID=304 STAMP=1032276949
channel ORA_DISK_2: piece handle=/nas/backups/T183/2020-02-13/2020-02-13_15:11/T183_akuoehsr_1_1.bkp tag=TAG20200213T151726
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:12:06
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_301_h4cj5yln_.arc thread=1 sequence=301
channel default: deleting archived log(s)
archived log file name=/u07/oradata/fast_recovery_area/T183/archivelog/2020_02_13/o1_mf_1_301_h4cj5yln_.arc RECID=303 STAMP=1032276947
media recovery complete, elapsed time: 00:10:33
Finished recover at 13-FEB-20
Step #5: Open the Database.
The final step to get the database up and running is to open it. This step also creates the online redo log files in the locations specified by ALTER DATABASE RENAME FILE commands in the RMAN script:
RMAN> alter database open resetlogs; Statement processed
Step #6: Assess the Results.
SQL> select name, dbid, cdb from v$database; NAME DBID CDB --------- ---------- --- T183 2832597398 YES
As expected, a backup of the T183 CDB database running on orasvr02 has been restored to orasvr01 with the same name and same DBID. If we want to register this database in RMAN’s Recovery Catalog, we will need to rename it and give it a new DBID.
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY T183_PDB1 MOUNTED SQL> alter pluggable database T183_PDB1 open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY T183_PDB1 READ WRITE
The T183_PDB1 PDB looks OK. Let’s check the archive log mode:
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------- db_recovery_file_dest string /u07/oradata/fast_recovery_area db_recovery_file_dest_size big integer 10G
So the database gets restored in the same archive log mode as the source database. Cool. Let’s find out where the rest of the files are:
SQL> select file_name from cdb_data_files;
FILE_NAME
-----------------------------------------------------------------------------------------------
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf
SQL> select file_name from cdb_temp_files;
FILE_NAME
------------------------------------------------------------------------------------
/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tmp
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------
/u03/oradata/T183/onlinelog/redo_g3m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log
/u03/oradata/T183/onlinelog/redo_g2m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log
/u03/oradata/T183/onlinelog/redo_g1m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u03/oradata/T183/controlfile/
T183_ctrl_1.ctl, /u07/oradata/
fast_recovery_area/T183/contro
lfile/T183_ctrl_2.ctl
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Finally, check to see if the instance has registered with any available listeners. There’s one listener on this server called LISTENER_T122:
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2020 19:56:17 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr01.mynet.com)(PORT=1521))) STATUS of the LISTENER Alias LISTENER_T122 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 03-FEB-2020 22:26:34 Uptime 9 days 21 hr. 29 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/orasvr01/listener_t122/alert/log.xml Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orasvr01.mynet.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=orasvr01.mynet.com)(PORT=5500)) (Security=(my_wallet_directory=/u01/app/oracle/admin/T122/xdb_wallet)) (Presentation=HTTP)(Session=RAW)) Services Summary… Service "9dc2ae83f20b40ace0531200a8c05c14.mynet.com" has 1 instance(s). Instance "T183", status READY, has 1 handler(s) for this service… Service "T122.mynet.com" has 1 instance(s). Instance "T122", status READY, has 1 handler(s) for this service… Service "T122XDB.mynet.com" has 1 instance(s). Instance "T122", status READY, has 1 handler(s) for this service… Service "T183.mynet.com" has 1 instance(s). Instance "T183", status READY, has 1 handler(s) for this service… Service "T183XDB.mynet.com" has 1 instance(s). Instance "T183", status READY, has 1 handler(s) for this service… Service "t183_pdb1.mynet.com" has 1 instance(s). Instance "T183", status READY, has 1 handler(s) for this service… The command completed successfully
Everything looks good. Here’s a short list of things we can change to make this database ready for prime time:
• Create a password file.
• Change the database name and DBID.
• Change the file name paths.
• Create an SPFILE.
• Register the database in the RMAN Recovery Catalog.
• Backup the database.
Step #7: Create a Password File.
Let’s check to see what we’re starting with:
SQL> select * from v$pwfile_users;
no rows selected
[oracle@orasvr01 ~]$ which orapwd
/u01/app/oracle/product/18.3.0/dbhome_1/bin/orapwd
[oracle@orasvr01 ~]$ orapwd FILE='/u01/app/oracle/product/18.3.0/dbhome_1/dbs/orapwT183' FORMAT=12.2
Enter password for SYS: <your-sys-password>
[oracle@orasvr01 ~]$ ls -l /u01/app/oracle/product/18.3.0/dbhome_1/dbs/ora*
-rw-r----- 1 oracle oinstall 6144 Feb 13 20:05 /u01/app/oracle/product/18.3.0/dbhome_1/dbs/orapwT183
SQL> col USERNAME format a30
col SYSDBA format a10 heading 'SYSDBA'
col SYSOPER format a10 heading 'SYSOPER'
col SYSASM format a10 heading 'SYSASM'
col SYSBACKUP format a10 heading 'SYSBACKUP'
col SYSDG format a10 heading 'SYSDG'
col SYSKM format a10 heading 'SYSKM'
select USERNAME,
SYSDBA,
SYSOPER,
SYSASM,
SYSBACKUP,
SYSDG,
SYSKM
from V$PWFILE_USERS
order by
1;
USERNAME SYSDBA SYSOPER SYSASM SYSBACKUP SYSDG SYSKM
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE
SQL> grant sysbackup to C##RMANBACKUP;
Grant succeeded.
USERNAME SYSDBA SYSOPER SYSASM SYSBACKUP SYSDG SYSKM
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
C##RMANBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
SYS TRUE TRUE FALSE FALSE FALSE FALSE
Step #8: Change the Database Name and DBID.
Oracle provides a nifty little utility called NID to change the name and DBID of a database. NID can be used to change just the database name, just the DBID or both. If you change the database name, but not the DBID you don’t have to open the database with RESETLOGS. Also, NID does not update the database’s GLOBAL_NAME. You have to do that yourself with the SQL command, ALTER DATABASE RENAME GLOBAL_NAME TO <newname>.domain.
We’ll use NID to change T183 with DBID 2832597398 to D183 with a new DBID the utility will generate for us. Once that’s done, the output will direct us to make some additional changes. First, the instance needs to be shutdown and re-started with the database mounted:
SQL> select name, dbid, cdb from v$database;
NAME DBID CDB
--------- ---------- ---
T183 2832597398 YES
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
[oracle@orasvr01 ~]$ which nid
/u01/app/oracle/product/18.3.0/dbhome_1/bin/nid
[oracle@orasvr01 ~]$ nid TARGET=SYS DBNAME=D183
DBNEWID: Release 18.0.0.0.0 - Production on Fri Feb 14 10:07:16 2020
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Password: <your-sys-password>
Connected to database T183 (DBID=2832597398)
Connected to server version 18.3.0
Control Files in database:
/u03/oradata/T183/controlfile/T183_ctrl_1.ctl
/u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl
Change database ID and database name T183 to D183? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2832597398 to 614983068
Changing database name from T183 to D183
Control File /u03/oradata/T183/controlfile/T183_ctrl_1.ctl - modified
Control File /u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl - modified
Datafile /u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSTEM_FNO-5.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-SYSAUX_FNO-6.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/64A52F53A7693286E053CDA9E80AED76/datafile/data_D-T183_TS-UNDOTBS1_FNO-8.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.db - dbid changed, wrote new name
Datafile /u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tm - dbid changed, wrote new name
Datafile /u03/oradata/T183/9795EEED00203DFFE0531200A8C06D7B/datafile/o1_mf_temp_h4cl8zxl_.tm - dbid changed, wrote new name
Datafile /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tm - dbid changed, wrote new name
Control File /u03/oradata/T183/controlfile/T183_ctrl_1.ctl - dbid changed, wrote new name
Control File /u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to D183.
Modify parameter file and generate a new password file before restarting.
Database ID for database D183 changed to 614983068.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
The “wrote new file” output is a little misleading. The paths to the data files are exactly the same as they were before. Hence, T183 is still part of the directory path even though the database is now called D183. We’ll fix that. The first thing to do is to add an entry to the /etc/oratab file so we can set our environment appropriately:
D183:/u01/app/oracle/product/18.3.0/dbhome_1:N
[oracle@orasvr01 ~]$ . oraenv ORACLE_SID = [T183] ? D183 The Oracle base remains unchanged with value /u01/app/oracle [oracle@orasvr01 ~]$ env | grep ORA ORACLE_SID=D183 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
Next, we’ll update the PFILE by making 3 changes to these parameters:
*.audit_file_dest
*.control_files.
*.db_name.
The previous value of *.audit_file_dest was:
*.audit_file_dest='/u01/app/oracle/admin/T183/adump'
Change it to the following value, then create the appropriate directory:
*.audit_file_dest='/u01/app/oracle/admin/D183/adump'
[oracle@orasvr01 ~]$ cd /u01/app/oracle/admin/ [oracle@orasvr01 admin]$ ls -l drwxr-x--- 7 oracle oinstall 4096 Nov 15 18:49 T122 drwxr-x--- 4 oracle oinstall 4096 Feb 12 18:24 T183 [oracle@orasvr01 admin]$ mkdir -p ./D183/adump;chmod 750 ./D183;chmod 750 ./D183/adump [oracle@orasvr01 admin]$ ls -l drwxr-x--- 3 oracle oinstall 4096 Feb 14 10:30 D183 drwxr-x--- 7 oracle oinstall 4096 Nov 15 18:49 T122 drwxr-x--- 4 oracle oinstall 4096 Feb 12 18:24 T183
The previous value of *.control_files was:
*.control_files='/u03/oradata/T183/controlfile/T183_ctrl_1.ctl',
'/u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl'
Change it to the following values, then copy the control files to their new locations:
*.control_files='/u03/oradata/D183/controlfile/D183_ctrl_1.ctl',
'/u07/oradata/fast_recovery_area/D183/controlfile/T183_ctrl_2.ctl'
[oracle@orasvr01 ~]$ cd /u03/oradata [oracle@orasvr01 oradata]$ mkdir -p ./D183/controlfile [oracle@orasvr01 oradata]$ chmod 750 ./D183;chmod 750 ./D183/controlfile [oracle@orasvr01 oradata]$ cp /u03/oradata/T183/controlfile/T183_ctrl_1.ctl ./D183/controlfile/D183_ctrl_1.ctl [oracle@orasvr01 oradata]$ cd /u07/oradata/fast_recovery_area [oracle@orasvr01 fast_recovery_area]$ mkdir -p ./D183/controlfile [oracle@orasvr01 fast_recovery_area]$ chmod 750 ./D183;chmod 750 ./D183/controlfile [oracle@orasvr01 fast_recovery_area]$ cp /u07/oradata/fast_recovery_area/T183/controlfile/T183_ctrl_2.ctl ./D183/controlfile/D183_ctrl_2.ctl
The previous value of *.db_name was:
*.db_name='T183'
Change it to the following value:
*.db_name='D183'
Lastly, we need to change a couple of files names:
[oracle@orasvr01 ~]$ cd $ORACLE_HOME/dbs [oracle@orasvr01 dbs]$ mv initT183.ora initD183.ora [oracle@orasvr01 dbs]$ mv orapwT183 orapwD183 [oracle@orasvr01 dbs]$ ls -l -rw-rw---- 1 oracle oinstall 1544 Feb 14 10:07 hc_T183.dat -rw-r--r-- 1 oracle oinstall 1327 Feb 14 11:13 initD183.ora -rw-r----- 1 oracle oinstall 24 Feb 13 10:29 lkT183 -rw-r----- 1 oracle oinstall 11264 Feb 13 20:52 orapwD183
The final proof that we’ve changed everything we need to is to startup the instance and open the database. Note, we’re using a FRA and OMF so Oracle will create the archivelog directory in the FRA for us. If we weren’t using those configuration settings, we’d need to pre-create the directory containing the archived redo logs.
[oracle@orasvr01 dbs]$ sqlplus / as sysdba Connected to an idle instance. SQL> startup mount pfile=/u01/app/oracle/product/18.3.0/dbhome_1/dbs/initD183.ora ORACLE instance started. Total System Global Area 2147481064 bytes Fixed Size 8898024 bytes Variable Size 603979776 bytes Database Buffers 1526726656 bytes Redo Buffers 7876608 bytes Database mounted. SQL> alter database open resetlogs; Database altered.
Step #9: Change the File Name Directory Paths.
Before we change anything else, let’s assess what we have:
SQL> col USERNAME format a30
col SYSDBA format a10 heading 'SYSDBA'
col SYSOPER format a10 heading 'SYSOPER'
col SYSASM format a10 heading 'SYSASM'
col SYSBACKUP format a10 heading 'SYSBACKUP'
col SYSDG format a10 heading 'SYSDG'
col SYSKM format a10 heading 'SYSKM'
set linesize 120
select USERNAME,
SYSDBA,
SYSOPER,
SYSASM,
SYSBACKUP,
SYSDG,
SYSKM
from V$PWFILE_USERS
order by
1;
USERNAME SYSDBA SYSOPER SYSASM SYSBACKUP SYSDG SYSKM
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
C##RMANBACKUP FALSE FALSE FALSE TRUE FALSE FALSE
SYS TRUE TRUE FALSE FALSE FALSE FALSE
SQL> select name, dbid, cdb from v$database; NAME DBID CDB --------- ---------- --- D183 614983068 YES SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY T183_PDB1 MOUNTED SQL> alter pluggable database T183_PDB1 open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY T183_PDB1 READ WRITE
SQL> select file_name from cdb_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------
/u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf
/u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf
SQL> select file_name from cdb_temp_files;
FILE_NAME
------------------------------------------------------------------------------------
/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp
/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tmp
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------
/u03/oradata/T183/onlinelog/redo_g3m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log
/u03/oradata/T183/onlinelog/redo_g2m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log
/u03/oradata/T183/onlinelog/redo_g1m1.log
/u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u03/oradata/D183/controlfile/
D183_ctrl_1.ctl, /u07/oradata/
fast_recovery_area/D183/contro
lfile/D183_ctrl_2.ctl
The password file is OK and the control files are in the right place. The data files, temp files and online redo log files all need to be moved to a directory path containg D183 rather than T183. Easy! First, pre-create all the necessary directories:
[oracle@orasvr01 D183]$ cd /u03/oradata/D183 [oracle@orasvr01 D183]$ ls -l drwxr-x--- 2 oracle oinstall 4096 Feb 14 11:07 controlfile drwxr-x--- 2 oracle oinstall 4096 Feb 14 11:28 onlinelog [oracle@orasvr01 D183]$ mkdir datafile;chmod 750 datafile [oracle@orasvr01 D183]$ mkdir -p ./9DC2AE83F20B40ACE0531200A8C05C14/datafile [oracle@orasvr01 D183]$ chmod 750 ./9DC2AE83F20B40ACE0531200A8C05C14 [oracle@orasvr01 D183]$ chmod 750 ./9DC2AE83F20B40ACE0531200A8C05C14/datafile [oracle@orasvr01 ~]$ cd /u07/oradata/fast_recovery_area/D183 [oracle@orasvr01 D183]$ ls -l drwxr-x--- 3 oracle oinstall 4096 Feb 14 11:27 archivelog drwxr-x--- 3 oracle oinstall 4096 Feb 14 11:37 autobackup drwxr-x--- 2 oracle oinstall 4096 Feb 14 11:09 controlfile drwxr-x--- 2 oracle oinstall 4096 Feb 14 11:28 onlinelog
Looks good. Let’s deal with the online redo logs first. The simplest way to achive the desired result is to just create additional online redo log file groups and members in the correct locations, then drop the original online redo log groups. This avoids having to take the database offline, copy the online redo log files manually, then use a series of ALTER DATABASE RENAME DATAFILE commands. I mean, that’s hard work! Let’s get the group numbers and file sizes before we start to add new groups and members:
SQL> set linesize 120
col member format a70
select l.group#,lf.member,((l.bytes/1024)/1024) "MB"
from v$log l, v$logfile lf
where l.group# = lf.group#
order by
l.group#;
GROUP# MEMBER MB
----------- ----------------------------------------------------------------- ----------
1 /u03/oradata/T183/onlinelog/redo_g1m1.log 200
1 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log 200
2 /u03/oradata/T183/onlinelog/redo_g2m1.log 200
2 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log 200
3 /u03/oradata/T183/onlinelog/redo_g3m1.log 200
3 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log 200
We also need to check our OMF online redo log parameter settings. Two defined destinations will ensure when we create a new group, Oracle will create 2 members:
SQL> show parameter db_create_online_log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------- db_create_online_log_dest_1 string /u03/oradata db_create_online_log_dest_2 string /u07/oradata/fast_recovery_area db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string
SQL> alter database add logfile group 4 size 200M; Database altered. SQL> alter database add logfile group 5 size 200M; Database altered. SQL> alter database add logfile group 6 size 200M; Database altered.
Re-query the online redo log file configuration to check we have achieved the desired result:
GROUP# MEMBER MB
---------- ---------------------------------------------------------------------- ----------
1 /u03/oradata/T183/onlinelog/redo_g1m1.log 200
1 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log 200
2 /u03/oradata/T183/onlinelog/redo_g2m1.log 200
2 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log 200
3 /u03/oradata/T183/onlinelog/redo_g3m1.log 200
3 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log 200
4 /u03/oradata/D183/onlinelog/o1_mf_4_h4fvqhnr_.log 200
4 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_4_h4fvqjqp_.log 200
5 /u03/oradata/D183/onlinelog/o1_mf_5_h4fvxvg2_.log 200
5 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_5_h4fvxwl1_.log 200
6 /u03/oradata/D183/onlinelog/o1_mf_6_h4fw2jjr_.log 200
6 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_6_h4fw2kgg_.log 200
Now we can proceed to drop groups 1, 2 and 3 making sure activity has been switched away from these groups before we drop them:
SQL> set linesize 120
col member format a70
select l.group#,lf.member,l.status,l.archived
from v$log l, v$logfile lf
where l.group# = lf.group#
order by
l.group# ,lf.member;
GROUP# MEMBER STATUS ARC
---------- ---------------------------------------------------------------------- ---------------- ---
1 /u03/oradata/T183/onlinelog/redo_g1m1.log CURRENT NO
1 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g1m2.log CURRENT NO
2 /u03/oradata/T183/onlinelog/redo_g2m1.log UNUSED YES
2 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g2m2.log UNUSED YES
3 /u03/oradata/T183/onlinelog/redo_g3m1.log UNUSED YES
3 /u07/oradata/fast_recovery_area/T183/onlinelog/redo_g3m2.log UNUSED YES
4 /u03/oradata/D183/onlinelog/o1_mf_4_h4fvqhnr_.log UNUSED YES
4 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_4_h4fvqjqp_.log UNUSED YES
5 /u03/oradata/D183/onlinelog/o1_mf_5_h4fvxvg2_.log UNUSED YES
5 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_5_h4fvxwl1_.log UNUSED YES
6 /u03/oradata/D183/onlinelog/o1_mf_6_h4fw2jjr_.log UNUSED YES
6 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_6_h4fw2kgg_.log UNUSED YES
We can drop groups 2 and 3 straight away, then switch away from group 1:
SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter system switch logfile; System altered.
OK, I cheated by bouncing the instance to get the status of group 1 to go INACTIVE quicker. I didn’t want to wait. Then I dropped group 1. Here’s the new online redo log file configuration:
GROUP# MEMBER STATUS ARC
---------- ---------------------------------------------------------------------- ---------------- ---
4 /u03/oradata/D183/onlinelog/o1_mf_4_h4fvqhnr_.log INACTIVE YES
4 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_4_h4fvqjqp_.log INACTIVE YES
5 /u03/oradata/D183/onlinelog/o1_mf_5_h4fvxvg2_.log CURRENT NO
5 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_5_h4fvxwl1_.log CURRENT NO
6 /u03/oradata/D183/onlinelog/o1_mf_6_h4fw2jjr_.log UNUSED YES
6 /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_6_h4fw2kgg_.log UNUSED YES
The previous online redo log files are still on disk despite being in an OMF location. Technically they’re not OMF files because we named them explicitly. Removing them will be part of clean up which comes later. Next, we can move the CDB’s data files which are currently here:
FILE_NAME ------------------------------------------------------------- /u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf /u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf /u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf /u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf
Since we’re using OMF, we can take advantage of that to move and rename the files to OMF standards:
SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u03/oradata SQL> alter database move datafile '/u03/oradata/T183/datafile/data_D-T183_TS-USERS_FNO-7.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/datafile/data_D-T183_TS-UNDOTBS1_FNO-4.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/datafile/data_D-T183_TS-SYSTEM_FNO-1.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/datafile/data_D-T183_TS-SYSAUX_FNO-3.dbf'; Database altered. SQL> select file_name from dba_data_files; FILE_NAME --------------------------------------------------------------------------------------------------- /u03/oradata/D183/datafile/o1_mf_users_h4fxqg6p_.dbf /u03/oradata/D183/datafile/o1_mf_undotbs1_h4fxrv3o_.dbf /u03/oradata/D183/datafile/o1_mf_system_h4fy7gbp_.dbf /u03/oradata/D183/datafile/o1_mf_sysaux_h4fzd43b_.dbf
Next, we move onto relocating the data files belonging to the PDB, T183_PDB1:
SQL> alter session set container=T183_PDB1; Session altered. SQL> select file_name from dba_data_files; FILE_NAME ---------------------------------------------------------------------------------------------- /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf SQL> alter database move datafile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSTEM_FNO-17.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-SYSAUX_FNO-18.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-UNDOTBS1_FNO-19.dbf'; Database altered. SQL> alter database move datafile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/data_D-T183_TS-USERS_FNO-20.dbf'; Database altered. SQL> select file_name from dba_data_files; FILE_NAME --------------------------------------------------------------------------------------------------- /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_system_h4g2b8yb_.dbf /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_sysaux_h4g3z387_.dbf /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_undotbs1_h4g4tvj7_.dbf /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_users_h4g5v123_.dbf
Next, let’s deal with the temp files. Staying in the PDB T183_PDB1:
SQL> select file_name, ((bytes/1024)/1024) "MB" from dba_temp_files; FILE_NAME MB ------------------------------------------------------------------------------------- ---------- /u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tmp 62 SQL> alter tablespace temp add tempfile size 62M; Tablespace altered. SQL> alter database tempfile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tmp' offline; Database altered. SQL> alter database tempfile '/u03/oradata/T183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4clb50m_.tmp' drop including datafiles; Database altered. SQL> select file_name, ((bytes/1024)/1024) "MB" from dba_temp_files; FILE_NAME MB ------------------------------------------------------------------------------------- ---------- /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4g6ch8f_.tmp 62
Now perform a similar operation in CDB$ROOT:
SQL> alter tablespace temp add tempfile size 20M; Tablespace altered. SQL> alter database tempfile '/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp' offline; Database altered. SQL> alter database tempfile '/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp' drop including datafiles; alter database tempfile '/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp' * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time
I got no time for this, so I bounced the instance to move things along:
SQL> alter database tempfile '/u03/oradata/T183/datafile/o1_mf_temp_h4cl7b6k_.tmp' drop including datafiles; Database altered. SQL> select file_name, ((bytes/1024)/1024) "MB" from dba_temp_files; FILE_NAME MB ---------------------------------------------------- ---------- /u03/oradata/D183/datafile/o1_mf_temp_h4g6tl71_.tmp 20
Next we need to relocate the files belonging to PDB$SEED:
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> alter database move datafile
'/u03/oradata/T183/9795EEED00203DFFE0531200A8C06D7B/datafile/data_D-T183_TS-SYSTEM_FNO-5.dbf';
Database altered.
SQL> alter database move datafile
'/u03/oradata/T183/9795EEED00203DFFE0531200A8C06D7B/datafile/data_D-T183_TS-SYSAUX_FNO-6.dbf';
Database altered.
SQL> alter database move datafile
'/u03/oradata/T183/9795EEED00203DFFE0531200A8C06D7B/datafile/data_D-T183_TS-UNDOTBS1_FNO-8.dbf';
Database altered.
SQL> alter tablespace temp add tempfile;
Tablespace altered.
A broken SEED PDB won’t interrupt operations of CDB$ROOT or any user PDBs, but we should test that the PDB$SEED can be used to create additional PDBs, just in case:
SQL> create pluggable database TESTPDB admin user admin identified by
default tablespace users;
Pluggable database created.
SQL> alter pluggable database TESTPDB open;
Pluggable database altered.
If you don’t see any errors or warnings, then PDB$SEED is happy. Go ahead and drop the TESTPDB and remove its GUID directory:
SQL> select name, guid from v$pdbs; NAME GUID ------------ -------------------------------- PDB$SEED 9795EEED00203DFFE0531200A8C06D7B T183_PDB1 9DC2AE83F20B40ACE0531200A8C05C14 TESTPDB 9E933404CC507261E0531100A8C001D5 SQL> alter pluggable database TESTPDB close; Pluggable database altered. SQL> drop pluggable database TESTPDB including datafiles; [oracle@orasvr01 D183]$ pwd /u03/oradata/D183 [oracle@orasvr01 D183]$ ls -l drwxr-x--- 3 oracle oinstall 4096 Feb 14 17:15 9795EEED00203DFFE0531200A8C06D7B drwxr-x--- 3 oracle oinstall 4096 Feb 14 12:22 9DC2AE83F20B40ACE0531200A8C05C14 drwxr-x--- 3 oracle oinstall 4096 Feb 14 18:16 9E933404CC507261E0531100A8C001D5 drwxr-x--- 2 oracle oinstall 4096 Feb 14 11:07 controlfile drwxr-x--- 2 oracle oinstall 4096 Feb 14 16:12 datafile drwxr-x--- 2 oracle oinstall 4096 Feb 14 13:09 onlinelog [oracle@orasvr01 D183]$ rm -r ./9E933404CC507261E0531100A8C001D5
Step #10: Create an SPFILE and Re-start the Instance.
SQL> create spfile from pfile; File created. (re-start the instance) SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/18.3.0
Step #11: Update the RMAN Configuration.
The new D183 database will have the same RMAN configuration as the database whose backup we used to create it. Let’s check what that looks like:
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Sat Feb 15 11:21:45 2020 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: D183 (DBID=614983068) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name D183 are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/nas/backups/T183/%d_%U'; 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 BACKED UP 1 TIMES TO 'SBT_TAPE'; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/18.3.0/dbhome_1/dbs/snapcf_D183.f'; # default
Looks like the only thing we need to change is CHANNEL DEVICE TYPE DISK to /nas/backups/D183/%d_%U. We also need to create the directory /nas/backups/D183. Simple enough:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/nas/backups/D183/%d_%U'; old RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/nas/backups/T183/%d_%U'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/nas/backups/D183/%d_%U'; new RMAN configuration parameters are successfully stored RMAN> exit Recovery Manager complete. [oracle@orasvr01 ~]$ mkdir /nas/backups/D183
To register the database we need to log back into RMAN and connect to the Recovery Catalog:
[oracle@orasvr01 ~]$ rman target rmanbackup/rmanbackup using sysbackup Recovery Manager: Release 18.0.0.0.0 - Production on Sat Feb 15 11:34:32 2020 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: D183 (DBID=614983068) RMAN> connect catalog rco/rco@rmancat connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
A REPORT SCHEMA command followed by a query against V$LOGFILE should display the structure of all the database’s files and confirm no reference to T183:
RMAN> report schema; Report of database schema for database with db_unique_name D183 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 880 SYSTEM YES /u03/oradata/D183/datafile/o1_mf_system_h4fy7gbp_.dbf 3 1970 SYSAUX NO /u03/oradata/D183/datafile/o1_mf_sysaux_h4fzd43b_.dbf 4 315 UNDOTBS1 YES /u03/oradata/D183/datafile/o1_mf_undotbs1_h4fxrv3o_.dbf 5 270 PDB$SEED:SYSTEM NO /u03/oradata/D183/9795EEED00203DFFE0531200A8C06D7B/datafile/o1_mf_system_h4gbmcyl_.dbf 6 350 PDB$SEED:SYSAUX NO /u03/oradata/D183/9795EEED00203DFFE0531200A8C06D7B/datafile/o1_mf_sysaux_h4gbzq9d_.dbf 7 5 USERS NO /u03/oradata/D183/datafile/o1_mf_users_h4fxqg6p_.dbf 8 100 PDB$SEED:UNDOTBS1 NO /u03/oradata/D183/9795EEED00203DFFE0531200A8C06D7B/datafile/o1_mf_undotbs1_h4gchdb7_.dbf 17 280 T183_PDB1:SYSTEM YES /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_system_h4g2b8yb_.dbf 18 480 T183_PDB1:SYSAUX NO /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_sysaux_h4g3z387_.dbf 19 420 T183_PDB1:UNDOTBS1 YES /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_undotbs1_h4g4tvj7_.dbf 20 100 T183_PDB1:USERS NO /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_users_h4g5v123_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 2 100 PDB$SEED:TEMP 32767 /u03/oradata/D183/9795EEED00203DFFE0531200A8C06D7B/datafile/o1_mf_temp_h4gftd8b_.tmp 4 62 T183_PDB1:TEMP 62 /u03/oradata/D183/9DC2AE83F20B40ACE0531200A8C05C14/datafile/o1_mf_temp_h4g6ch8f_.tmp 5 20 TEMP 20 /u03/oradata/D183/datafile/o1_mf_temp_h4g6tl71_.tmp RMAN> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u03/oradata/D183/onlinelog/o1_mf_4_h4fvqhnr_.log /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_4_h4fvqjqp_.log /u03/oradata/D183/onlinelog/o1_mf_5_h4fvxvg2_.log /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_5_h4fvxwl1_.log /u03/oradata/D183/onlinelog/o1_mf_6_h4fw2jjr_.log /u07/oradata/fast_recovery_area/D183/onlinelog/o1_mf_6_h4fw2kgg_.log
An RMAN LIST BACKUP command should only display details of autobackups written to the FRA. To protect the work we’ve done up to this point, you should take a full database backup.
Step #12: Cleanup.
Over the previous 11 steps we have restored a database called T183 then renamed it D183. There will still be fragments of T183 hanging around on the server and should be cleaned up. Specifically, check these directories and delete what shouldn’t be there:
[oracle@orasvr01 ~]$ cd $ORACLE_BASE/audit [oracle@orasvr01 audit]$ ls -l drwxr-x--- 6 oracle oinstall 4096 Feb 14 18:23 D183 drwxr-x--- 2 oracle oinstall 4096 Nov 15 18:46 T122 drwxr-x--- 3 oracle oinstall 4096 Feb 6 12:46 T183 [oracle@orasvr01 audit]$ cd /u03/oradata [oracle@orasvr01 oradata]$ ls -l drwxr-x--- 7 oracle oinstall 4096 Feb 14 18:37 D183 drwxr-x--- 8 oracle oinstall 4096 Feb 13 16:09 T183 [oracle@orasvr01 oradata]$ cd /u07/oradata/fast_recovery_area [oracle@orasvr01 fast_recovery_area]$ ls -l drwxr-x--- 6 oracle oinstall 4096 Feb 14 11:37 D183 drwxr-x--- 7 oracle oinstall 4096 Dec 2 10:24 T122 drwxr-x--- 7 oracle oinstall 4096 Feb 12 18:14 T183 [oracle@orasvr01 fast_recovery_area]$ cd $ORACLE_HOME/dbs [oracle@orasvr01 dbs]$ ls -l -rw-rw---- 1 oracle oinstall 1544 Feb 14 16:28 hc_D183.dat -rw-rw---- 1 oracle oinstall 1544 Feb 14 10:07 hc_T183.dat -rw-r----- 1 oracle oinstall 24 Feb 14 11:27 lkD183 -rw-r----- 1 oracle oinstall 24 Feb 13 10:29 lkT183 -rw-r----- 1 oracle oinstall 11264 Feb 14 11:35 orapwD183 -rw-r----- 1 oracle oinstall 18825216 Feb 15 12:04 snapcf_D183.f -rw-r----- 1 oracle oinstall 4608 Feb 15 12:04 spfileD183.ora
And that’s how you can restore a database backup to a different server, recover it to a previous point in time while switching from ASM storage to file system storage. There are little short cuts you can make to this approach which would eliminate or reduce some of the steps, but this is essentially what you would need to do. Phew! 😦
