We can also export tablespace[s] with the help of TABLESPACES parameter in data pump utility.
In this, we can backup only the tables contains in specified set of tablespaces. Table will be backup with the dependent objects.
Note: In this mode both object metadata and data is backup.
Syntax:
TABLESPACES = tablespace_1, tablespace_2, [,..] Continue reading
We can export single as well as multiple schema’s/users with the help of SCHEMAS data pump parameter.
Consider following hands-on on exporting schema/user with the help of data pump utility:
Note:
If EXP_FULL_DATABASE role not granted to user who performing export operation, then user can only perform export his schema not others. (default)
Here we are exporting scott schema ( Single schema ) from orcl database:
expdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_export.log
…
. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:58:35
Importing above exported schema(scott):
impdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_import.log
–x–
we can also export multiple schema’s with the help of same SCHEMAS data pump parameter.
In following case, we are exporting scott & test schema’s from orcl database;
expdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_export.log
…
. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
. . exported “TEST”.”DEPT” 0 KB 0 rows
. . exported “TEST”.”EMP” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_test_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 12:07:36
Importing above exported schemas(scott & test):
impdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_import.log
…
. . imported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . imported “SCOTT”.”BONUS” 0 KB 0 rows
. . imported “TEST”.”DEPT” 0 KB 0 rows
. . imported “TEST”.”EMP” 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 12:09:06
–x–
Likewise we can Export-Import multiple schema’s with the help of data pump utility.
************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.
We can export single as well as multiple tables with the help of TABLES data pump parameter.
Consider following hands-on on exporting tables with the help of data pump utility.
//Consider example on single table export, in this we are exporting table emp from scott schema. Continue reading
If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.
RMAN database point-in-time recovery (DBPITR) restores the db from RMAN backups.
RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time. Continue reading
According to standard practice, we should consider multiplexing of online redo log files to avoid such a scenarios, Each log file group should have more than/at least 2 log file members & location of all group on different physical disk. ( In case of worst situation with disk 1 then database would be recovery with the help of disk 2 – Online redo log file )
Single current online redo log file is sufficient to restore the entire database & do an incomplete recovery. Continue reading
We have two methods to unregister database from RMAN recovery catalog.
Method 1st:
Unregister target database with the help of “Unregister database” RMAN command. Continue reading
In case of worst situation with datafile & controlfile, we can repair/restore it with the help of RMAN data recovery advisor.
Note:
We can use Data Recovery Advisor with the help of Oracle Enterprise Manager, Grid Control & RMAN command prompt.
Lets consider hands-on on on RMAN repair advisor with the help of following case studies: Continue reading
How to enable Block Change Tracking for RMAN incremental backup performance gain.
Prior to Oracle 10g, RMAN incremental backup has to scan whole datafile & read each and every single block to identified changed blocks, in short RMAN incremental backup is as equal as RMAN full backup. Continue reading
To clear the saved configuration in RMAN, please consider following examples for better understanding:
Connect to RMAN prompt:
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 – Production on Thu Jan 9 07:09:36 2014 Continue reading
This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.
Login as sysdba and issue the following script:
This script will report on all currently running RMAN backups like full, incremental & archivelog backups:
SQL> col STATUS format a9 SQL> col hrs format 999.99 SQL> select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key; SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .00 SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .01 SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL COMPLETED 01/07/14 10:28 01/07/14 10:29 .03
Above script will give you RMAN backup status along with start and stop timing.
OR
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK; SID SERIAL# CONTEXT SOFAR TOTALWORK %COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 18 29 1 9115569 19258880 47.33
Above script will give you SID, Total Work, Sofar & % of completion.
You can also check historical backup status with the help of following script:
set linesize 500 pagesize 2000 col Hours format 9999.99 col STATUS format a10 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time, to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time, elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS order by session_key; SESSION_KEY INPUT_TYPE STATUS RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME HOURS ----------- ------------- ---------- ------------------- ------------------- -------- 137764 DB FULL COMPLETED 06-06-2017 02:00:32 06-06-2017 04:12:13 2.19 137770 ARCHIVELOG COMPLETED 06-06-2017 04:00:29 06-06-2017 04:01:05 .01 137778 ARCHIVELOG COMPLETED 06-06-2017 06:00:27 06-06-2017 06:00:35 .00 137782 ARCHIVELOG COMPLETED 06-06-2017 08:00:32 06-06-2017 08:03:36 .05 137786 ARCHIVELOG COMPLETED 06-06-2017 10:00:30 06-06-2017 10:02:03 .03 137790 ARCHIVELOG COMPLETED 06-06-2017 12:00:30 06-06-2017 12:02:34 .03 137794 ARCHIVELOG COMPLETED 06-06-2017 14:00:30 06-06-2017 14:02:58 .04 . .. ...
*****
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have an easy life ahead.
RMAN is a oracle utility to backup, restore & recovery of database.
The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)
Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration. Continue reading
