Data Pump Export and Import

Introduced in Oracle Database 10g, Data Pump provides a server-based data extraction and import tool. Its features include improved architecture and functionality of original import and export applications. Data Pump allows you to stop and restart a job, see the status of the service running, and prevent data export and import.

NOTE: Data Pump files are incompatible with those generated from the original Export utility.

Data Pump runs as a server process, benefiting users in many ways. The client process that starts the job can disconnect and later reattach to the job. Performance is enhanced (as compared to the original Export/Import) because the data no longer has to be processed by a client program. Data Pump extractions and loads can be parallelized, further enhancing performance. In this section, you will see how to use Data Pump, along with descriptions and examples of its major options. This includes how Data Pump uses directory objects, specifying options on the command line, and how to stop and restart jobs within the Data Pump command-line interface.

DATAPUMP Architecture :

Certain enhancements in DATAPUMP cannot be achieved using traditional exp/imp. One of the most important ones is the “Restartability” of the exp/imp process. I will try to go through most of the useful features (with examples) that are new in Oracle 10g and above. The movement of data across the databases and servers is an integral part of the enterprise architecture. There has always been the need to move the subset of data from one database to another database or from one server to another. This was achieved using the exp/imp tools of Oracle in earlier versions. With Oracle 10g, we have been provided with the DATAPUMP utility, which provides fast parallel bulk data and metadata movement between databases… simple and fast. With DATAPUMP Oracle has introduced a setup, which the utility creates by itself. The various components of the setup are described below. The architecture of the Data Pump:

In Oracle 10g the Data Pump (server-side utility) does all the work. Earlier the exp/imp utilities were executed from the client and the dump file was stored on the client. With Data Pump the dump files are stored on the server. The Data Pump architecture consists of the following components.

• Data Pump API
• Client Tools
• Master Control Process
• Worker Process

Data Pump API: DBMS_DATAPUMP is the API for Data Pump. This API is the engine using which the jobs are created and monitored.

Client Tools: As in the earlier version, we had exp/imp tools, now we have data pump tools expdp and impdp. These client-side tools use the DBMS_DATAPUMP API to execute and monitor the jobs.

Master Control Process: The Master Control Process (MCP) controls the execution of the data pump job. There is one MCP per job. It maintains the control information like job name, job status, file information, etc. in the master table, which has the same name as the job name. The details in the master table can be used to restart a job. Once the job is completed, the master table is dropped. At the heart of the Data Pump operation is the Master Table. This table is created at the start of a Data Pump operation and deleted at the end of a successful Data Pump operation. The Master Table can be deleted if the job is deleted using the interactive kill_job command. If a job is stopped using the stop_job command or if a job is stopped unexpectedly, The Master Table will be saved. The keep_master parameter can be set to Y to persist the master table at the end of a successful job for maintenance purposes. The name of the Master Table is the same as the Data Pump job name.

Worker Process: Worker process are created by the MCP depending on the PARALLEL parameter. The worker process performs the tasks as requested the MCP process which is the loading/unloading of data or metadata. The worker process maintains the details in the master table which can be used to restart the job.

The advantages of using a Data Pump are :

• ability to estimate job times
• ability to restart failed jobs
• perform fine-grained object selection
• monitor running jobs
• directly load a database from a remote instance via the network
• remapping capabilities
• improved performance using parallel executions

A couple of notes are that you cannot export to a tape device only to disk, and the import will only work with the version of Oracle 10.1 or greater. Also, remember that the expdp and impdp are command-line tools and run from within the Operating System.

Data Pump Uses: You can use the Data Pump for the following

• migrating databases
• copying databases
• transferring Oracle databases between different operating systems
• backing up important tables before you change them
• moving database objects from one tablespace to another
• transporting tablespaces between databases
• reorganizing fragmented table data
• extracting the DDL for tables and other objects such as stored procedures and packages

How does a Data Pump work?

The Master Control Process (MCP), has the process name DMnn, only one master job runs per job which controls the whole Data Pump job, it performs the following

1. create jobs and control them
2. creates and manages the worker processes
3. monitors the jobs and logs the process
4. maintains the job state and restart information in the master table (created in the user schema running the job)
5. manages the necessary files including the dump file set.

The master process creates a master table that contains job details (state, restart info), this table is created in the user's schema who is running the Data Pump job. Once the job has finished it dumps the table contents into the data pump file and deletes the table. When you import the data pump file it re-creates the table and reads it to verify the correct sequence in which it should import the various database objects.

The worker process is named DWnn and is the process that actually performs the work, you can have several worker processes running on the same job (parallelism). The work process updates the master table with the various job statuses.

The shadow process is created when the client logs in to the Oracle server it services data pump API requests, and it creates the job consisting of the master table and the master process.

The client processes are the expdp and impdp commands.

Data Pump files 

You will use three types of files when using the data pump, all files will be created on the server.

Dump Files - holds the data and metadata

Log Files - the resulting output from the data pump command

SQL Files - contain the DDL statements describing the objects included in the job but can contain data

Master Data Pump Tables - when using the data pump it will create tables within the schema, this is used for controlling the data pump job, and the table is removed when finished.

Five modes of Data Pump exports are supported:

•  Full: Export of all database data and metadata
•  Schema: Export data and metadata for specific user schemas
•  Tablespace: Export data and metadata for tablespaces
•  Table: Export data and metadata for tables and table partitions
•  Transportable Tablespace: Export metadata for specific tablespaces in preparation for Transporting a tablespace from one database to another

NOTE: You must have the EXP_FULL_DATABASE system privilege to perform a Full export or a Transportable Tablespace export.

The basics of RMAN(Oracle’s Recovery Manager)

Oracle Recovery Manager (RMAN) takes backup and recovery to a new level of security and ease of use. Since the appearance of RMAN in Oracle version 8, many changes have occurred. key improvements and enhancements that can make RMAN a "one-stop shop" solution for almost any database, including Oracle multitenancy providers Architecture features are available for the first time in Oracle Database 12c. In addition to the RMAN CLI upgrade in Oracle 12c, all RMAN features have been added. Enterprise Manager Cloud Control 12c (EM Cloud Control) web interface, allows DBA to monitor and perform backup operations when only a web browser connection is available. we'll use several RMAN performance examples, using the command line interface. An example will cover the scope of RMAN Configuring the environment for backup and recovery and backup itself. We will enter some details about how RMAN manages the metadata associated with the database as well as Background. Finally, we will cover several different topics, such as using RMAN catalog backups performed outside the RMAN environment.

RMAN Features and Components

RMAN is not the only client side that can be used with a web interface. It includes many other things, including the database to be supported (the target database), an optional recovery catalog, an optional recovery zone, and media management software for the backup tape support system. We will briefly examine each of them in this section Many features of RMAN have no equivalent to the backup methods presented in Chapter 13. We will compare the pros and cons of using RMAN with a more traditional systems background.

RMAN components 

• Target Database - the Oracle database in which the backup or recovery operations are being performed.
• RMAN Client – executable that interprets commands, directs server sessions to execute those commands, and records its activity in the target database control file (or catalog). 
• Fast Recovery Area (Optional) - a disk location that you can use to store recovery-related files such as control files and online redo log copies, archived redo log files, flashback logs, and RMAN backups. 
• Media Manager (Optional) - an application required for RMAN to interface with sequential media devices such as tape libraries. A media manager controls these devices during backup and recovery, managing the loading, labeling, and unloading of media.
• Recovery Catalog (Optional) - A separate database schema used to record activity against one or more target databases. A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file.

RMAN vs. Traditional Backup Methods

There are very few reasons not to use RMAN as your primary backup management tool. Here are some of the major features of RMAN that are not available with traditional backup methods or have significant restrictions using traditional backup methods:

■ Skip unused blocks: Blocks that have never been written to, such as blocks above the 

high-water mark (HWM) in a table, are not backed up by RMAN when the backup is an 

RMAN backupset. Traditional backup methods have no way of knowing which blocks have 

been used.

■ Backup Compression: In addition to skipping blocks that have never been used, RMAN can also use one of several Oracle-specific binary compression modes to save space on the backup device. Although operating system–specific compression techniques are available with traditional backup methods, the compression algorithm used by RMAN is customized to maximize the compression for the typical kinds of data found in Oracle data blocks. Although there is a slight increase in CPU time during an RMAN compressed backup or recovery operation, the amount of media used for backup may be significantly reduced, as well as network bandwidth if the backup is performed over the network. Multiple CPUs can be configured for an RMAN backup to help alleviate the compression overhead.

■ Open Database Backups: Tablespace backups can be performed in RMAN without using the BEGIN/END BACKUP clause with ALTER TABLESPACE. Whether using RMAN or a traditional backup method, however, the database must be in ARCHIVELOG mode.

■ True Incremental Backups: For any RMAN incremental backup, unchanged blocks since the last backup will not be written to the backup file. This saves a significant amount of disk space, I/O time, and CPU time. For restore and recovery operations, RMAN supports incrementally updated backups. Data blocks from an incremental backup are applied to a previous backup to potentially reduce the amount of time and number of files that need to be accessed to perform a recovery operation. We will cover an example of an incrementally updated backup later in this chapter.

■ Block-level Recovery: To potentially avoid downtime during a recovery operation, RMAN supports block-level recovery for recovery operations that only need to restore or repair a small number of blocks identified as being corrupt during the backup operation. The rest of the tablespace and the objects within the tablespace can remain online while RMAN repairs the damaged blocks. The rows of a table not being repaired by RMAN are even available to applications and users.

■ Table-level Recovery: When logical backups of a table aren’t available or when FLASHBACK TABLE cannot bring back a table to a previous state, you can use RMAN to restore a table or tables from an RMAN backup as of any SCN since the last full RMAN backup with the database in ARCHIVELOG mode. This makes table-level recovery much easier than having to restore and recover an entire tablespace, much less the entire database, for just one table.

■ Multiple I/O Channels: During a backup or recovery operation, RMAN can utilize many I/O channels, via separate operating system processes, to perform concurrent I/O. Traditional backup methods, such as a Unix cp command, are typically single-threaded operations.

■ Platform Independence: Backups written with RMAN commands will be syntactically identical regardless of the hardware or software platform used, with the only difference being the media management channel configuration. On the other hand, a Unix script with lots of cp commands will not run very well if the backup script is migrated to a Windows platform!

■ Tape Manager Support: All major enterprise backup systems are supported within RMAN by a third-party media management driver provided by a tape backup vendor.

■ Cataloging: A record of all RMAN backups is recorded in the target database control file, and optionally in a recovery catalog stored in a different database. This makes restore and recovery operations relatively simple compared to manually tracking operating system–level backups using “copy” commands.

■ Scripting Capabilities: RMAN scripts can be saved in a recovery catalog for retrieval during a backup session. The tight integration of the scripting language, the ease of maintaining scripts in RMAN, and the Oracle scheduling facility make it a better choice than storing traditional operating system scripts in an operating system directory with the operating system’s native scheduling mechanisms.

■ Encrypted Backups: RMAN uses backup encryption integrated into Oracle Database 12c (including advanced compression) to store encrypted backups. Storing encrypted backups on tape requires the Advanced Security Option. In a few limited cases, a traditional backup method may have an advantage over RMAN; but now that RMAN supports the backup of password files and other non-database files such as tnsnames.ora, listener.ora, and sqlnet.ora (using Oracle Secure Backup), the case for RMAN as your single backup and recovery solution is compelling.

Backup Types 

RMAN supports several different backup methods, depending on your availability needs, the desired size of your recovery window, and the amount of downtime you can endure while the database or a part of the database is involved in a recovery operation.

Consistent and Inconsistent Backups

A physical backup can be classified by being a consistent or an inconsistent backup. In a consistent backup, all data files have the same SCN; in other words, all changes in the redo logs have been applied to the data files. Because an open database with no uncommitted transactions may have some dirty blocks in the buffer cache, an open database backup can rarely be considered consistent. As a result, consistent backups are taken when the database is shut down normally or in a MOUNT state.In contrast, an inconsistent backup is performed while the database is open and users are accessing the database. Because the SCNs of the data files typically do not match when an inconsistent backup is taking place, a recovery operation performed using an inconsistent backup must rely on both archived and online redo log files to bring the database into a consistent state before it is opened. As a result, a database must be in ARCHIVELOG mode to use an inconsistent backup method.

Full and Incremental Backups

Full backups include all blocks of every data file within a tablespace or a database; it is essentially a bit-for-bit copy of one or more data files in the database. Either RMAN or an operating system command can be used to perform a full backup, although backups performed outside of RMAN must be cataloged with RMAN before they can be used in an RMAN recovery operation.In Oracle 11g and later, incremental backups can be level 0 or level 1. A level 0 backup is a full backup of all blocks in the database that can be used in conjunction with differential, incremental, or cumulative incremental level 1 backup in a database recovery operation. A distinct advantage to using an incremental backup in a recovery strategy is that archived and online redo log files may not be necessary to restore a database or tablespace to a consistent state; the incremental backups may have some or all of the blocks needed. An example of using level 0 and level 1 incremental backups is presented later in this chapter. Incremental backups can only be performed within RMAN.

Image Copies

Image copies are full backups created by operating system commands or RMAN BACKUP AS COPY commands. Although a full backup created with a Unix cp command can be later registered in the RMAN catalog as a database backup, doing the same image copy backup in RMAN has the advantage of checking for corrupt blocks as they are being read by RMAN and recording the information about the bad blocks in the data dictionary. Image copies are the default backup file format in RMAN. This is a great feature of Oracle 12c’s RMAN for the following reason: If you add another datafile to a tablespace, you need to also remember to add the new datafile to your Unix script cp command. By creating image copies using RMAN, all datafiles will automatically be included in the backup. Forgetting to add the new datafile to a Unix script will make a recovery situation extremely inconvenient at best and a disaster at worst.

Backupsets and Backup Pieces

In contrast to image copies, which can be created in almost any backup environment, backup sets can be created and restored only with RMAN. A backup set is an RMAN backup of part or all of a database, consisting of one or more backup pieces. Each backup piece belongs to only one backupset and can contain backups of one or many datafiles in the database. All backup sets and pieces are recorded in the RMAN repository, the same as any other RMAN-initiated backup.

Compressed Backups

For any Oracle 12c RMAN backup creating a backupset, compression is available to reduce the amount of disk space or tape needed to store the backup. Compressed backups are only usable by RMAN, and they need no special processing when used in a recovery operation; RMAN automatically decompresses the backup. Creating compressed backups is as easy as specifying AS COMPRESSED BACKUPSET and COMPRESSION ALGORITHM in the RMAN BACKUP command or as part of the default settings.

Data Guard Management

 You have three options on which to manage Data Guard

SQL* Plus 

Guard Broker - a distributed management tool configuring management using the DGMGRL command line.

Enterprise Manager - provides the data vendor with a graphical representation that replaces the DGMGRL 

To use Enterprise Manager, you must have a data broker installed. The vendor maintains a configuration file that includes profiles for all databases. Changes can be propagated to all databases in the configuration. The vendor also includes a command to start the monitor, a process that monitors the status of the database configuration and performs automatic shutdowns.

FIGURE 1-01. Primary Database Archiving When There Is No Standby Database

You may think that the database manager is one point of failure, which needs to be corrected: the sales process is the basis of any database in the organization and communication. If the system you entered goes down, connect to another database in the settings and restart the control.

Apply Services

There are two ways to apply recovery, Redo Apply (physical standby) and SQL Apply (logical standby). Both have the same characteristics: 

• Both share basic data 
• Both can prevent data exchange 
• Both provide a high degree of isolation between the primary database and the standby database. 
• Both can be easily switched from the standby database to the primary database 
• Both of them provide a standby database that does not affect the primary database.

Redo Apply(physical standby)

Redo apply is essentially a block-by-block physical copy of the main database, redo apply uses media recovery to read records from SRL into memory and apply the change vectors directly to the standby database. 

Media Recovery performs simultaneous recovery for high performance, it includes Media Recovery Manager (MRP0) and multiple parallel application processes (PR0?). The scheduler manages the recovery time, integrates the redo through the SCN through multiple sessions (if in an RAC environment), and converts the redo into a change map of the partitioned application process. 

The application process reads the data block, collects the variables from the graph, and adds the variables to the data block. This method allows you to use the database as read-only. Active data protection checks for readable text changes in previous versions using the SCN "query". 

The media recovery process in the standby database promotes SCN queries after all transaction-based changes have been made. The SCN query is revealed to the user through the current_scn column of the v$database view. Using read-only will only be able to see data up to the SCN query, so the standby database can be opened in read-only mode while media recovery is active, making it a standby database. 

You can use SYNC or ASYNC and separate it from physical I/O corruption. A corruption investigation is conducted on the following points: 

 In the main when you restore transport - LGWR, LNS, ARCH use parameter DB_UTRA_SAFE. 

Sleep during recovery - RFS, ARCH, MRP, and DBWR use DB_BLOCK_CHECKSUM and DB_LOST_WRITE_PROTECT parameters. 

If Data Protection detects corruption, it will automatically restore a new copy of the master file data using the difference resolution method in the hope that the original data is free of corruption. 

The main feature of this solution is 

• Full application and data transparency - no data types or other restrictions 
• The performance is very high, the complexity is not controlled, and a small part of the movement 
• End-to-end validation before application, including corruption due to lost text 
• It can be used for read-only queries and reports while providing disaster recovery 
• Can perform database upgrade rolling from Oracle Database 11g.

SQL Apply(Logical Standby)

SQL Apply uses a logical fallback process (LSP) to configure a variable application in a standby database. SQL Apply requires more configuration than Redo Apply, the processes that make up SQL Apply, read SRL and "I" redo by converting it to a logical change record, then create a SQL transaction and apply SQL to the standby database. Since there are more moving parts, it requires more CPU, memory, and I/O, and it slows down the application. 

 SQL Apply does not support all data types, such as XML in object-relational formats, and Oracle-provided formats such as Oracle Spatial, Oracle intermedia, and Oracle text. The advantage of SQL Apply is that the database is open for reading and writing while the application is running, as you cannot make any changes to the duplicate data that you may have entered. 

change and delete data from local tables and features added to the database, you can create virtual views and local entries. This makes it ideal for using reporting tools etc.

The main feature of this solution is 

• The standby database is open for reading while the SQL application is running 
• Security settings that prevent changes to data stored by SQL applications. 
• Revolving database upgrades can be done from Oracle Database 11g using the KEEP IDENTITY clause 

Synchronous :

Synchronous transport (SYNC) is also called the "zero data loss" method because the LGWR does not let it know that the commitment was successful until the LNS can confirm that the return required to recover the data transaction is recorded in the backup site.

         

  FIGURE 1-02. LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

In the diagram on the right, the order of business is 

 

The user performs a transaction by creating a redo log in the SGA, LGWR reads the redo log from the log buffer, writes it to the online redo file, and waits for confirmation from the LNS. 

LNS reads the same redo record from storage and transfers it to the standby database using Oracle Net Services. RFS retrieves the recovery from the standby database and writes to SRL. 

When the RFS receives a write from disk, it transmits an acknowledgment to the LNS system in the backend database which, in turn, informs the LGWR that the transmission is complete. LGWR sends support to support staff. 

This configuration is really dependent on network performance and can have a significant impact on back-end databases. Low network outages will have a significant impact on response times. This effect is observed in the SENDREQ wait event \" LNS wait in the v$system_event dynamic performance check.

There is also a timeout value that can change in the event of a network outage, we will discuss this in detail in the installation section.

Asynchronous :

Asynchronous transport (ASYNC) differs from SYNC in that it eliminates the requirement for LGWR to wait for confirmation from the LNS, thus creating "zero side" performance in the main database, n 'regardless of the distance between the primary and secondary locations. 

LGWR will continue to recognize the success of the promise even if bandwidth prevents the transmission of previous transactions from being sent directly to the standby database. If the LNS cannot maintain and restore the backup log before the restore is sent to the standby server, 

LNS automatically switches to reading and sending from a log file instead of a buffer in the SGA. Once the LNS is captured, it returns to reading directly from the SGA buffer. The log buffer rate is checked with the X$LOGBUF_READHIST check. A low success rate indicates that the LNS is reading from the log file instead of saving the log. If this happens, try increasing the size of the log buffer.

                                         

                  FIGURE 1-03. LGWR ASYNC Archival with Network Server (LNSn) Processes

The downside of ASYNC is the increased risk of data loss. If a failure destroys the primary database before the transit time is reduced to zero, all committed transactions that are part of the transit delay are lost. So again make sure that the network bandwidth is sufficient and that you are getting the lowest latency.

Oracle recently released an advanced compression option. This new product has many compression features, one of which is redo compression for data storage, and it supports both SYNC and ASYNC. 

Like all compression tools, it has an impact on CPU resources but will reduce network bandwidth usage.

Oracle Data Guard Architecture

The exception in the log file occurs whenever the database continues to execute transactions when the LNS process stops redo transfers to the standby database (network problem). 

The main database continues to write to the current log file, fills it up, then switches to the new log file, and starts archiving the file. Before you know it, many archive and log files need to be processed. from LNS, creating a large space in the log file. The database uses the ARCH method in the primary database to continuously ping the database during outages. When the database is finally returned, the ARCH process queries the standby control file (through the RFS process) to determine the last complete record file received from the source for the previous day. 

The ARCH process will transfer the missing files to the standby database using another ARCH process. During the next record exchange, the LNS will attempt and succeed in establishing a connection to the standby database and initiate continuous retransmissions as the ACH process settles. space behind. Once the failover application process can capture the current redo log, the application process automatically switches from reading the redo log to reading the current SRL.