Oracle: Data Pump and RMAN backup/restore cheat sheet
expdp/impdp commands, flashback exports, cold and hot backups, and common RMAN operations (list, validate, restore, housekeeping).
This article is a quick reference for logical exports and imports with Oracle Data Pump (expdp / impdp) and for physical backups with RMAN (cold backup, hot backup, restore and housekeeping). Schema, directory and credential names are generic examples.
Data Pump
Directories
Data Pump reads and writes through an Oracle DIRECTORY object.
select directory_name, directory_path from dba_directories;
create directory dp_dir as '/u02/oracle/datapump';
grant read, write on directory dp_dir to app_user;
-- change an existing directory
create or replace directory data_pump_dir as '/u03/oracle/data_pump/';
grant read, write on directory data_pump_dir to dba;
Monitor a running job
SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork;
set linesize 400
set pagesize 10000
column username format A10
column sid format 99999999999
column opname format A40
column target format A40
column %DONE format A10
SELECT b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
FROM v$session_longops b, v$session a
WHERE a.sid = b.sid
ORDER BY 6;
Export a schema
Add compression=all to reduce the dump size.
date=$(date '+%Y%m%d%H%M%S')
expdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir compression=all parallel=4 dumpfile=exp_appschema_${date}.dmp logfile=exp_appschema_${date}.log
When you cannot connect as system, connect as sysdba:
expdp \"sys/<password>@mydb as sysdba\" SCHEMAS=APPSCHEMA DIRECTORY=dp_dir DUMPFILE=appschema.dmp LOGFILE=appschema.log
Export for an older target version
Check the database version, then use the VERSION parameter to make the dump readable by the older release:
select * from v$version;
expdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir compression=all parallel=4 VERSION=11.2 dumpfile=expdp_appschema_${date}.dmp logfile=expdp_appschema_${date}.log
Import a schema
impdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir parallel=4 DUMPFILE=exp_appschema_20220111092532.dmp LOGFILE=imp_appschema.log
To import into another schema, use remap_schema (and remap_tablespace if the tablespace differs):
impdp system/<password> SCHEMAS=APPSCHEMA DIRECTORY=dp_dir remap_schema=APPSCHEMA:APPSCHEMA_TEST dumpfile=exp_appschema_20191007170920.dmp logfile=imp_appschema_test.log
impdp app_user/<password> directory=data_pump_dir dumpfile=app.dmp remap_schema=APP:APP5 remap_tablespace=users:accounting partition_options=none
If the target schema already exists and must be replaced, drop it first (close its sessions if needed):
SELECT sid, serial#, status FROM v$session WHERE username = 'APPSCHEMA';
ALTER SYSTEM KILL SESSION '784,36993' IMMEDIATE;
DROP USER appschema CASCADE;
Find the schema names inside a dump file
Use sqlfile= to generate the DDL contained in the dump instead of importing it:
impdp app_user/<password> directory=dp_dir dumpfile=app.dmp logfile=app.log sqlfile=app_ddl.txt
Search the generated file for CREATE USER statements to see which schemas the import needs. For example:
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "APP" IDENTIFIED BY VALUES '<PASSWORD>'
DEFAULT TABLESPACE "APPDATA"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "APP";
GRANT CREATE SESSION TO "APP";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "APP";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "APP" DEFAULT ROLE ALL;
The drawback is that the SQL file can be very large when the dump is large.
Consistent export with flashback
Get the current SCN, then pass it to flashback_scn:
SELECT current_scn FROM v$database;
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
SELECT SCN_TO_TIMESTAMP(5474751) FROM dual;
expdp system/<password> schemas=APPSCHEMA directory=dp_dir flashback_scn=5474280 dumpfile=expdp_appschema_${date}.dmp logfile=expdp_appschema_${date}.log
You can also flash back to a time. In a parameter file:
flashback_time="to_timestamp('09-05-2011 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"
Escaped on the command line:
expdp ..... flashback_time=\"to_timestamp\(\'09-05-2011 09:00:00\', \'DD-MM-YYYY HH24:MI:SS\'\)\"
Import directly over a database link
With network_link, no intermediate dump file is written on the source. Create the link first:
create database link SRC_DB connect to app_user identified by '<PASSWORD>' using 'SRC_DB';
grant CREATE DATABASE LINK to app_user;
impdp system/<password>@TARGET_DB SCHEMAS=APPSCHEMA DIRECTORY=dp_dir parallel=4 network_link=SRC_DB LOGFILE=imp_network.log
Move a dump between servers
tar -cjvf expd_appschema.tar.bz2 *
scp oracle@source-host:/u02/oracle/datapump/expd_appschema.tar.bz2 .
tar -xjvf expd_appschema.tar.bz2
Restore a single table under another name
expdp system/<password> directory=dp_dir dumpfile=expdp_table_${date}.dmp logfile=expdp_table.log tables=APPSCHEMA.MY_TABLE
impdp system/<password> schemas=APPSCHEMA REMAP_TABLE=MY_TABLE:MY_TABLE_RESTORE directory=dp_dir dumpfile=expdp_table.dmp logfile=impdp_table.log indexes=N constraints=N triggers=N EXCLUDE=STATISTICS
Related exclusions: EXCLUDE=CONSTRAINT, EXCLUDE=INDEX_STATISTICS (excludes index statistics) and EXCLUDE=TABLE_STATISTICS (excludes table statistics).
Tables without a segment (deferred segment creation)
Tables created with deferred segment creation have no segment until the first insert, and are skipped by some exports. To disable the feature and list the affected tables:
alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;
show parameter DEFERRED_SEGMENT_CREATION
SELECT table_name FROM dba_tables WHERE owner = 'APPSCHEMA' AND segment_created = 'NO';
Allocate an extent for an existing table:
ALTER TABLE appschema.my_table ALLOCATE EXTENT;
RMAN
Inspect the current setup and backups
export NLS_DATE_FORMAT='yyyy-mm-dd:hh24:mi:ss'
rman target /
SHOW ALL;
REPORT SCHEMA;
LIST BACKUP SUMMARY;
LIST BACKUP;
LIST BACKUP OF DATABASE SUMMARY;
LIST BACKUP OF ARCHIVELOG ALL SUMMARY;
LIST BACKUP without SUMMARY gives the detail; add TAG="xxx" to filter on a tag.
Validate a backup without restoring
Simulate a restore:
RESTORE DATABASE PREVIEW;
List the backup pieces needed for recovery (adapted from a dbi-services article, see the links below). The script writes the list of files to a text file named after the timestamp and SCN:
echo "restore controlfile preview; restore database preview;" | NLS_DATE_FORMAT='yyyymmddhh24miss' rman target / | awk '
/Finished restore at /{timestamp=$4}
/Recovery must be done beyond SCN /{if ($7>scn) scn=$7 }
/^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,"") ; files[$0]=1 }
END{ for (i in files) print i > "files-"timestamp"-SCN-"scn".txt" } '
Cold backup (database shut down)
List the files to copy:
SELECT name FROM v$datafile
UNION
SELECT member FROM v$logfile
UNION
SELECT name FROM v$controlfile
UNION
SELECT name FROM v$tempfile;
Then, in SQL*Plus, run shutdown immediate, copy the files (for example into a tar archive), and run startup. To restore, extract the archive with tar -xvf and start the database.
The same cold backup can be scripted with RMAN:
rman target / << EOF
shutdown immediate
startup mount;
backup database format '/backup/TESTDB_%d_%t_%s.rman';
alter database open;
EOF
Hot backup (database open)
The database must be in ARCHIVELOG mode. To enable it:
shutdown immediate
startup mount
alter database archivelog;
alter database open;
Check that it is active with archive log list;, select log_mode from v$database; or ps -ef | grep -i _arc. Check the Fast Recovery Area with show parameter db_recovery_file_dest.
Without RMAN, put the database in backup mode, copy the files (same list as for a cold backup), then end backup mode:
alter database begin backup;
-- copy the files (tar ...)
alter database end backup;
To restore: shutdown immediate, extract the files, startup. An error message appears after the startup, which is expected; then run alter database end backup;. The status can be checked with select * from v$backup;.
With RMAN, switch the log file first:
alter system switch logfile;
Full backup with archived logs
run {
ALLOCATE CHANNEL ch_1 DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/df_%d_%t_%s_%p' MAXPIECESIZE 10G;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup tag 'DAILY_INC_L0' incremental level 0 database plus archivelog delete all input;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
release channel ch_1;
}
Other forms:
backup database plus archivelog delete input;
backup as compressed backupset database format '/oracle/backup/%U';
To change the default backup location:
run {
allocate channel d1 device type disk format '/backups/%U.bkp';
backup database include current controlfile;
release channel d1;
}
Persistent configuration
Using a recovery catalog is recommended (register database; in RMAN).
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/backup/%F';
CONFIGURE channel device type disk format '/u03/backup/%U' maxpiecesize 8 G;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
Move old backups to another folder
backup backupset all format '/u03/lt_backup/%U' delete input;
backup backupset completed before 'TRUNC(SYSDATE)+1.5/24' format '/u03/lt_backup/%U' delete input;
Restore to a point in time
run {
allocate channel ch_1 type disk;
set until time "to_date('2019-01-29:14:06:48', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
}
After an incomplete recovery, open the database with:
alter database open resetlogs;
To restore until a log sequence, first look at the current sequences:
select sequence#, first_change#, to_char(first_time,'HH24:MI:SS') from v$log;
Housekeeping: crosscheck and delete
CROSSCHECK compares the backups recorded in the control file (or the recovery catalog) with the files on disk and marks missing ones as expired.
crosscheck backup;
crosscheck backup of database;
crosscheck backup of archivelog all;
list expired backup;
list expired backup of database;
list expired backup of archivelog all;
delete noprompt expired backup;
DELETE NOPROMPT OBSOLETE;
To maintain from a dedicated channel:
allocate channel for maintenance device type disk;
crosscheck backup;
delete noprompt expired backup;
release channel;
To save a listing to a file:
spool log to '/path/list.txt'
list backupset;
spool off
Legacy exp / imp
exp userid=system/<password> owner=app_schema file=app_schema.dmp log=exp_app_schema.log
imp system/<password> fromuser="app_schema" touser="app_schema" file="app_schema.dmp" commit=Y ignore=Y log=imp_app_schema.log
Create the target schema before importing. After the import, recompile the objects if the schema has procedures, packages or triggers, with utlrp.sql from $ORACLE_HOME/rdbms/admin.
Notes
- Never put a real password on the command line: use a password prompt, a wallet or a protected parameter file.
- Use a
DIRECTORYpath on a file system with enough free space, and make sure theoracleOS user owns it. - Always verify a backup with
RESTORE DATABASE PREVIEWand test restores on a non-production system.