Archive for the ‘RMAN’ Category

PDB Point-in-Time Recovery and Flashback in Oracle 20c

In DBA, New features, Oracle database, RMAN on July 10, 2020 at 14:41

The most significant point about the Oracle 20c database architecture is that non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container database in 20c and your data will reside within a pluggable database.

But then how about if you need to restore one PDB to any time in the recent past?

In Oracle database 20c, flashback and PITR (=point-in-time recovery) are supported when recovering PDBs to an ancestor or orphan PDB incarnations. These operations were not possible in 19c and below. Just as a reminder, in Oracle 12.1 flashback database operations were possible on root container level and thus affected all PDBs under the root container. Oracle 12.2 started supporting flashback of a PDB.

There is one restriction though in 20c: you cannot perform PDB flashback or PITR operation to a PDB incarnation within an orphan database incarnation. In other words, you can flashback as long as the CDB incarnation does not change. Or restated: you can flashback a PDB to an orphan PDB incarnation that is either within the same CDB incarnation or in an ancestor CDB incarnation. Also, flashback of a PDB to an orphan incarnation is supported only when the database uses local undo.

Now, this might confusing. Let me first shortly explain what is an ancestor incarnation and an orphan incarnation.

Database incarnations have the following relationships to each other:

– The current incarnation is the one in which the database is currently operating
– The incarnation from which the current incarnation originated after an OPEN RESETLOGS operation is the parent incarnation of the current incarnation
– The parent of the parent incarnation is an ancestor incarnation and any parent of an ancestor incarnation is also an ancestor of the current incarnation
– A noncurrent incarnation that is not a direct ancestor of the current incarnation is called orphan incarnation

During the flashback of the PDB, Oracle modifies only the data files for that PDB. The data in the other PDBs is not impacted. The point in time for the flashback can be one of the following:

– System Change Number
– Specific time in the past
– CDB restore point
– PDB restore point
– PDB clean restore point
– PDB guaranteed restore point

Here is an example of how flashback to any time in the recent past works in Oracle 20c.

We have lost at 2:30pm a table called RDBMS_BRANDS and a materialized zone map RDBMS_ZMAP from a pluggable database called NOVOPDB2. We have a restore point called rp1_novo_pdb2 created before the “disaster” at 8am in the morning. So, let us first flashback and verify we get the 2 objects back:

SQL> select systimestamp from dual;

05-JUL-20 PM +00:00


no rows selected


Pluggable database altered.


Flashback complete.


Pluggable database altered.



Well, unfortunately, now we notice that slightly before noon time data was loaded into a new table called RDBMS_HISTORY which was not at 8am in the PDB. All SCNs between 8am and the current time are now on an orphan PDB incarnation. We will flahsback again using another restore point created at 12 o’clock.


Pluggable database altered.


Flashback complete.


Pluggable database altered.



SQL> select systimestamp from dual;

05-JUL-20 PM +00:00

So, we managed to flashback to a point few hours after we opened with RESETLOGS from the previous flashback. But within the same incarnation of the CDB.

DBAs can follow the process of restore and recovery using the V$SESSION_LONGOPS and V$RECOVERY_PROGRESS views, respectively.

For the restore, the V$SESSION_LONGOPS view’s column OPNAME should be ‘Flashback Database’. Just like this:

SELECT sofar, totalwork, units 
FROM v$session_longops 
WHERE opname = 'Flashback Database';

The column SOFAR shows the data currently read in megabytes while the column TOTALWORK shows the total number of megabytes of flashback logs that must be read.

Restore points are created with the following command:


Restore point created.

Oracle Zero Data Loss Recovery Appliance

In DBA, Oracle database, Oracle Engineered Systems, RMAN on October 30, 2014 at 17:57

During the early versions of Oracle, Larry Ellison was asked once if anyone ever asked for their money back. His answer was: “No, but they used to ask us for their DATA back.”


Backup and Recovery go hand-in-hand, right? It is just that before recovery, we need to restore the data under the solid assumption that there is something to restore.

From the 10 member family of Oracle Engineered Systems, the ZDLRA is in my opinion the most critical. Let me explain why including some useful information for system DBAs.


Granted we do not want to find solutions to problems we don’t have, let us look at what DBAs’ reality is today. Most problems listed below are not longer just DBA’s weekly burdens, they affect global business directly:

1. Data growth and long backup windows: system is slow and virtually unusable because of a long running backup, what are the options now: offload backups to an ADG site, use BCV splits, do not take backups at all?

2. Infinite availability: looks like people do not talk any more of four 9s or five 9s, more and more often I hear words like zero down time, infinite availability, continuous availability. An ex-colleague and friend of mine used to say: “Zero downtime exists only in power point presentations”.

3. Lack of backup validation and end-to-end visibility: according to the Oracle documentation, the main purpose of RMAN validation is to check for corrupt blocks and missing files. You can also use RMAN to determine whether backups can be restored. How often do we do that? Really!

4. Data loss and data corruption: one can write a book on this subject. I still sadly witness databases being backed up using storage replication. That is indeed a very fast way to backup corrupted data blocks!

Recovery Appliance provides the following benefits:

1. Elimination of Data Loss
2. Minimal Backup Overhead
3. Improved End-to-End Data Protection Visibility
4. Cloud-Scale Protection

Here is a sample picture of the Recovery Appliance Architecture (it is worth reading the details behind the link):


Core DBAs might be interested in the new DBMS_RA package. A DBA can use the DBMS_RA subprograms to perform all Recovery Appliance administration functions. Check the DBMS_RA Package Reference.

There 27 new views related to the ZDLRA. Check the Recovery Appliance View Reference for more details.

The database account RASYS owns the Recovery Appliance schema, which includes the RMAN recovery catalog and the DBMS_RA PL/SQL package. The RASYS user name is fixed and cannot be changed. RASYS does not have the privileges required to create database user accounts.

DBAs should know that the Zero Data Loss Recovery Appliance Backup Module is an Oracle-supplied SBT library that RMAN uses to transfer backup data over the network to the Recovery Appliance. An SBT library transfers data to and from a backup device type, either a tape device or Recovery Appliance. RMAN performs all backups to the Recovery Appliance, and all restores of complete backup sets, by means of this module.

The Recovery Appliance Backup Module must be installed in the following two locations: (1) in the ORACLE_HOME of every protected database that sends backups to a Recovery Appliance for Recovery Appliance replication environments, and (2) on every upstream Recovery Appliance that sends backups to downstream Recovery Appliances.

Another important new concept for DBAs is the protection policy one: it is a named collection of properties that you can assign to multiple protected databases. A default installation of Recovery Appliance has these 4 protection policies.

Finally, an important questions: which databases are supported? The following Oracle Database releases are:

    – Oracle Database releases 10.2 through require manual HTTPS configuration.
    – Oracle Database releases and 12.x are fully supported.

Four good links to start from:

1. Zero Data Loss Recovery Appliance Administrator’s Guide Release 12.1
2. Reinventing Database Protection
3. Data Sheet – Oracle Zero Data Loss Recovery Appliance
4. A Technical Guide to Oracle’s Recovery Appliance


Cross-Platform Transportable Database and Oracle Engineered Systems

In Consolidation, DBA, Exadata, Oracle database, RMAN, SuperCluster on May 25, 2014 at 15:25

Cross-platform transportable database is not the same thing as transportable tablespace. When performing x-platform transportable database we copy the entire database, including the SYSTEM and SYSAUX tablespaces from one platform to another. The usual containment checks are no longer needed and because the SYSTEM tablespace is also being copied, no metadata datapump export/import step is required. But cross-platform transportable database can only be performed between platforms that have the same endian format.


When consolidating a large number of databases onto Exadata or SuperCluster, the work has to be automated as much as possible. When the source and the target platform share the same endian (see the 2 endian group below), then the best option is to use the transportable database method.

Otherwise, there are 3 options:

1. Create a new database on the EXA platform manually, and transport the needed tablespaces from the source database using the Cross Platform Transportable Tablespace method.
2. If downtime during the upgrade is an issue, Cross Platform Incremental Backup is an extremely good option. You can Refresh Standby Databases using the method.
3. Use the new Full Transportable Export/Import method. Note that full transportable export is available starting while full transportable import is available starting


DBAs should consider the following points before the migrations:

– Some parts of the database cannot be transported directly: redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process (alter database backup controlfile to trace resetlogs;), and an OPEN RESETLOGS is performed once the new database is created.

– BFILEs are not transported. RMAN provides a list of objects using the BFILE datatype in the output for the CONVERT DATABASE command, but users must copy the BFILEs themselves and fix their locations on the destination database. Execute DBMS_TDB.CHECK_EXTERNAL in order to identify any external tables, directories or BFILEs.

– Tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run. After opening with resetlogs, run alter tablespace TEMP add tempfile…

– External tables and directories are not transported. RMAN provides a list of affected objects as part of the output of the CONVERT DATABASE command, but users must redefine these on the destination platform. Run select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES and ensure that the same paths are available on the target system.

– Password files are not transported. If a password file was used with the source database, the output of CONVERT DATABASE includes a list of all usernames and their associated privileges. Create a new password file on the destination database using this information.

– When all is done, run UTLIRP and UTLRP.

For SAP users, check Note 105047 – Support for Oracle functions in the SAP environment:

86. Transportable Tablespaces: the implicit use is supported in the BRSPACE function “-f dbcreate” (Note748434) and the “Tablespace Point in Time Recovery” function of BRRECOVER. Explicit use as part of system copying is tolerated.

87. Transportable database: Can be used (Note 1367451).

And finally, here is some more information on how to migrate your databases to Oracle Engineered Systems:

1. Exadata MAA Best Practices Series Session 6: Migrating to Exadata

2. Exadata MAA Best Practices Migrating Oracle Databases

3. Oracle’s SPARC M5-32 and SPARC M6-32 Servers: Domaining Best Practices


Twelve new DBA commands for Oracle 12c

In DBA, Init.ora, Oracle database, Oracle internals, RMAN on January 25, 2014 at 10:47

With more than 500 new features, Oracle Database 12c is designed to give us exactly what we need for easy database administration, consolidation, security and availability. And I cannot deny the complexity it brings into our pluggable world…


But straight to the point. Here are 12 new commands for Oracle DBAs:

1. impdp … transform=disable_archive_logging:Y

The new TRANSFORM option DISABLE_ARCHIVE_LOGGING appended to the impdp command line will cause Oracle Data Pump to disable (almost completely) redo logging when loading data into tables and when creating indexes. Check this example.

2. select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;

With the new DBMS_QOPATCH package we can view the installed database patches:

GET_OPATCH_BUGS: Provides a bugs list for a patch
GET_OPATCH_COUNT: Provides the total number of installed patches
GET_OPATCH_LSINVENTORY: Returns whole opatch inventory
GET_OPATCH_PREQS: Provides prerequisite patches for a given patch

Check this example.

3. grant sysdba, sysoper, sysbackup, sysdg, syskm to c##julian identified by password container=all;

If we want to create a common user in the CDB, we must prefix it with c##. It is because the default common user prefix in 12.1.0 is c##. However, the _common_user_prefix parameter controls that prefix. One option is to set it to NULL.

SYSBACKUP, SYSDG and SYSKM are also database users. SYSDG is used for Oracle Data Guard operations: startup, shutdown and flashback database while SYSKM is used for encryption key management operations: connect to the database even if the database is not open.

4. create table porter(owner varchar2(128), object_name varchar2(128), created date invisble);


This “trick” does not work with temporary tables, external tables and cluster tables. Check this example.

5. alter system set max_string_size=extended;


The new init.ora parameter MAX_STRING_SIZE (default is standard) controls the maximum size of the VARCHAR2, NVARCHAR2, and RAW data types. It can be increased from 4,000 to 32767 bytes. Check this example.

6. alter database move datafile ‘old_location’ to ‘new_location’;


In 12.1.0, a data file can now be moved online while it is open and being accessed, all these are possible now:

– Renaming an Online Datafile
– Relocating an Online Datafile
– Copying an Online Datafile
– Relocating an Online Datafile and Overwriting an Existing File
– Relocating an Online Datafile to Oracle ASM
– Moving a File from One ASM Location to Another ASM Location

7. recover table emp until time ‘sysdate-1’;


RMAN enables us to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. We can use previously-created RMAN backups to recover tables and table partitions to a specified point in time. Check this example.

8. alter session set row archival visibility = active;
alter session set row archival visibility = all;

In-Database Archiving enables us to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. To manage In-Database Archiving for a table, we must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Check my old blog post.

9. alter session set temp_undo_enabled = true;

Check this example.

10. truncate table dept cascade;

TRUNCATE TABLE is enhanced in the following way: a new clause CASCADE allows you to recursively truncate child tables. Check this example.

11. create sequence temp_order_seq start with 100 increment by 10 session;

The new clause SESSION allows you to create a session sequence. Check this example.

12. $ORACLE_HOME/perl/bin/perl -n 8 -l $ORACLE_HOME/diagnostics catupgrd.sql

Oracle Database 12c introduces the Parallel Upgrade Utility (, which enables components that do not have to be upgraded in a specific order to be upgraded at the same time, taking full advantage of CPU capacity. The Parallel Upgrade Utility reduces the total amount of time it takes to perform an upgrade by loading the database dictionary in parallel using multiple SQL processes to upgrade the database.

-n specifies the number of processes to use for parallel operations (default = 4). The maximum is 8. Set this parameter to 0 to run in serial mode.

Restoring the Oracle database

In Bugs, Database tuning, DBA, Oracle database, RMAN on January 11, 2012 at 06:08

If you are reading this, there is high chance that someone has been breathing behind your back while you were restoring a database or mildly harassing you over the phone with the only question: when do you think that the database will be open for WebLogic connections?

Indeed when? There is no way to say precisely. You can guess based on the time the backup had taken in average but that is just an estimate. After the restore, there might be a nasty long sequence of archivelog files that has to be applied (with the hope that none is missing) and then you can open with resetlogs. Unless you hit a “feature” or two.

There are 5 major reasons why RMAN restore performance may be poor:

1. Missing statistics: Before a restore even begins the the control file is queried, datafile headers are read, media managers are initialised, the catalog is resynced, PL/SQL is generated and compiled, RMAN metadata either in the control file or the catalog is queried. All this incurs I/O against the control file, the datafile headers and the recovery catalog.

Make sure that the catalog database is analyzed and reorganized on regular basis. It also requires regular maintenance: crosscheck backup, etc.

Generate statistics on the fixed objects, with the GATHER_FIXED_OBJECTS_STATS procedure. This should be done when the database has been running for awhile with generic workload, so the information in the fixed objects reflects a reasonable state of the database load.

Gather the statistics with the following command:

exec dbms_stats.gather_fixed_objects_stats;

Believe it or now, often adding this to the RMAN script might help for both faster backup and restore:

alter session set optimizer_mode=RULE;

2. The backup is hardware multiplexed: all channels write to a single tape device. If the media manager does not support multiplexing only one backuppiece can be returned at a time. Considering the fact that each backuppiece is multiplexed with the rest of the others, this restore would require as many scans as the number of channels allocated of at most the whole backup size each (with a tape rewind after each scan).

3. Individual files or tablespaces are being restored: A restore of a single file needs a scan of potentially the whole database backup depending on where the header of the file being restored is positioned on the tape. The worst situation is when single file is being restored so that the file’s block header is the last block written to the backuppiece.

4. A different number of channels is used for restore compared to backup: The number of channels to be allocated should be equal to the number of physical tape drives. Try to do a couple of things:

Set enough big large pool: set LARGE_POOL_SIZE to at least 256M
Enable the backup_tape_io_slaves by setting BACKUP_TAPE_IO_SLAVES = true

However, some media managers will allow hardware multiplexing to a single tape and are able to parallelize the restore (in the media manager layer) such that the backuppieces are returned from a single tape to multiple channels in parallel. In such a situation it is possible to use an unpublished SET PARALLELMEDIARESTORE OFF command to make RMAN ignore this check.

      RUN { ...

5. The RMAN relation with bugs is not a new one. The “mend-it-or-end-it” principle does not play any role here. 5 years ago, Jaffar posted on his blog Known RMAN Performance Problems. It is worth checking again the MOS note 247611.1

Yet, here are some Oracle or MML bugs not fixed by January 1st, 2012:

Bug 12363733: restoring backups are very slow (reproduced both with asm and filesystem)
Bug 7573468: RMAN duplicate until time very slow
Bug 11835641: 11gr2 – RMAN restore took more time – duplicate or restore is slow
Bug 10210264: RMAN restore is slower following upgrade to 11.2
Bug 11827990: select name from v$datafile wait on “rdbms ipc reply” on rman restore
Bug 12543119: EM not using correct ASM diskgroup to restore datafiles when creating clone
Bug 11892765: please allow RMAN restore to pre-allocate file extents
Bug 9556740: RMAN job failed when using sudo
Bug 9724316: dbclone not choosing the correct controlfile for the clone
Bug 9845029: OEM clone 11gR2 ASM database fails during recovery
Bug 5381095: RMAN: poor restore performance in nocatalog mode
Bug 10093810: RMAN backup need to maintain an index for file header block for each file
Bug 9918138: RMAN restoring a compressed backup running very slow
Bug 6964596: RMAN single channel restore performance is faster than 6 channels
Bug 5462916: RMAN: recover database skip forever tbs with ORA-01157
Bug 2608118: restore of autobackup controlfile take a long time to complete
Bug 1533686: RMAN hangs when restoring via adsm with an ops target db
Bug 2911857: bad performance of RMAN duplication with large number of files (> 1500)
Bug 6770620: RMAN recover of copy database command consumes CPU

Bug 13109129: after upgrade to 11.2 compressed RMAN backups are slow
Bug 13066081: RMAN unregister database is slow/hangs
Bug 12896388: RMAN backup slower with – check logical – option on compressed tablespace
Bug 13500084: RMAN switch command is taking too long
Bug 13064833: RMAN process hangs after finished backup
Bug 13520050: RMAN session does not terminate- loops -spinning on cpu when destination is full

Some of the bugs will never be probably fixed as you can see that they may get, and are closed (or suspended) because of vendor OS problems, problem cannot be replicated, closed as duplicate, information not avaiable, etc.

Or how about those bugs with status 92: closed, not a bug. For example bug 11835641 is actually not a bug? Right.

Additional information: 11.2 Oracle Database Backup and Recovery User’s Guide. Check Part VI, Tuning and Troubleshooting.