Data Guard Configuration Parameters

DATA GUARD CONFIGURATION PARAMETERS

Description :
In this blog, we are going to see oracle Data Guard Configuration parameters. and how to view and set the parameters.

Types of parameter configuration.


Independent parameters.
Primary database parameters.
Standby database parameters.

Independent parameters:
DB_UNIQUE_NAME:
DB_UNIQUE_NAME specifies a globally unique name for the database. must have a unique DB_UNIQUE_NAME for every database.it can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (_), the number sign (#), and dollar sign ($).
db_unique_name=’unique_name’

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
—————————–       ———– —————
db_unique_name              string      orcl

LOG_ARCHIVE_CONFIG:
        LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.
          We can add send,nosend,receive,noreceive,dgconfig,nodgconfig to this log_archive_parameter.
log_archive_config=’db_config=(Primary,Standby)’
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME                                 TYPE        VALUE
——————————— ———– ——————-
log_archive_config            string      dg_config=(orcl,stand)
change value to log_archive_config:
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(orcl,stand)’ SCOPE=both;
System altered.

LOG_ARCHIVE_MAX_PROCESSES:
LOG_ARCHIVE_MAX_PROCESSES specifies the number of archiver background processes (ARC0 through ARC9) Oracle initially invokes.
log_archivemax_processes=’4′

DB_CREATE_FILE_DEST:
DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles.
db_create_file_dest=’+DATA’
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u02/oradata’;

Primary database parameters:
LOG_ARCHIVE_DEST_n:
The LOG_ARCHIVE_DEST_n initialization parameter defines up to ten destinations, each of which must specify either the LOCATION or the SERVICE attribute to specify where to archive the redo data. Location and service attributes are mandatory for the configuration.
SERVICE – it can be a tnsnames entry to identify the database.
SYNC – synchronous redo transmission.
ASYNC – Asynchronous redo transmission.

LOGARCHIVE_DEST_STATE_n:
Specify the availability state of the corresponding destination.
enabled:
Specifies that a valid log archive destination can be used for a subsequent archiving operation
defer:
Specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enabled.
alternate:
Specifies that a log archive destination is not enabled but will become enabled if communications to another destination fail.

Standby Database Parameters:
DB_FILE_NAME_CONVERT:
            It converts the filename of a new data file on the primary database to a filename on the standby database.
SQL> show parameter DB_FILE_NAME_CONVERT
NAME                                            TYPE         VALUE
———————————    ———– ——————————
db_file_name_convert                 string      /u01/app/oracle/oradata/ORCL/
                                                                        , /u01/app/oracle/oradata/STAND/

LOG_FILE_NAME_CONVERT:
          LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database.
SQL> show parameter LOG_FILE_NAME_CONVERT
NAME                                                     TYPE         VALUE
———————————                        ——–        ————
log_file_name_convert                       string          /u01/app/oracle/oradata/ORCL/
                                                                        , /u01/app/oracle/oradata/STAND
alter system set db_file_name_convert=’/u02/oradata/orcl/datafile’ scope=spfile;
alter system set log_file_name_convert=’orcl’,’stand’ scope=spfile;

FAL_SERVER:
FAL means fetch archive log. It clearly indicates the name that fetches the archive log.
FAL_SERVER is used to fetch an archive log server for a standby database.
Value in FAL_SERVER parameter act as Oracle Net Service name which points to the standby database.
ALTER SYSTEM SET fal_server=’stand ‘SCOPE=both;
ALTER SYSTEM SET fal_client=’orcl ‘SCOPE=both;

FAL_CLIENT:
FAL_CLIENT specifies the FAL(fetch archive log) client name that is used by the FAL service.
It is configured for point the FAL Client.
Value in FAL_CLIENT is also an Oracle Net Service Name.
ALTER SYSTEM SET fal_server=’orcl ‘SCOPE=both;
ALTER SYSTEM SET fal_client=’stand’SCOPE=both;

STANDBY_FILE_MANAGEMENT:
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
MANUAL:
Disables automatic standby file management.
AUTO:
Enables automatic standby file management.
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Data Guard Architecture

Description:
In this blog, we are going to see oracle Data Guard architecture and a clear description of the data guard overall process.

What is Oracle Data Guard:

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
Data Guard maintains these standby databases as transactionally consistent copies of the production database.
If the production database becomes unavailable, Data Guard can switch any standby database to the production role.

Data Guard configuration pre-system checks:
Hardware :
       Hardware can be different for the standby and primary databases. Like CPU, hard disk capacity.
Operating system :
The operating system should be the same as both the primary and standby databases, if you use Linux on the primary side the same os will be the standby. OS versions can be different but the platform should be the same.
Software :
The same release of oracle database version in primary and standby side.
         Standby database enables archive and force logging method.
          Sysdba privilege required.

ORACLE DATA GUARD ARCHITECTURE:
Primary Database Process:

LGWR(log writer)
            Log writer collects transaction log information and updates to the online redo logs.
      LGWR modes:
            SYNC– in synchronous mode, it sends redo information directly to the RFS(remote file server) process on the standby database its waits for the confirmation before proceeding.
            ASYNC – in asynchronous mode its also sends redo information directly it does not wait before proceeding.
            In ASYNC mode LGWR submits the network I/O request to the network server (LNSn) process for the destination.

Archiver process(ARCn):
            ARCn or a SQL session performing an archival operation, create a copy of online redo logs locally for primary database recovery.
            ARCn also send simultaneously online logs to the RFS(remote file server)
            It’s also responsible for GAP solving in the standby database.

FAL (Fetch Archive Logs):
FAL provides a client/server mechanism for resolving gaps detected in the range of archive logs generated in the primary database and received to the standby database.

Standby Database Process:

RFS (remote file server process):
RFS it receives redo information from the primary database. RFS writes redo to standby redo logs or archived logs
Each primary LSN, ARCn has their own RFS

ARC archiver process;
ARCn process archives all standby redo logs.

Managed Recovery Process(MRP):
            For the physical standby database, only MRP applies archived log information to the physical standby database.
            MRP process commands:
                        Alter database recover managed standby database disconnect no delay.
                        Alter database recover managed standby database disconnect from the session.
                        Alter database recover managed standby database cancel.

Logical standby database:
LSP is controlling the applies of archived redo log information to the logical standby database.

Standby redo log files:
Standby redo log files are used only when the database is in the standby role to store redo data received from the primary database.
Standby redo logs from a separate pool of log filegroup.

********************************************************************************

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Datapump Utilities Import Parameters

[oratest@oracle ~]$ impdp help=y

Import: Release 19.0.0.0.0 - Production on Mon Aug 9 22:52:15 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed 
within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH,
EXTERNAL_TABLE, and INSERT_AS_SELECT.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT, ENABLE_NETWORK_COMPRESSION,
REJECT_ROWS_WITH_REPL_CHAR, SKIP_CONSTRAINT_ERRORS, CONTINUE_LOAD_ON_FORMAT_ERROR,
TRUST_EXISTING_TABLE_PARTITIONS and VALIDATE_TABLE_DATA.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo is suppressed while standard input is read.

ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [YES].

HELP
Display help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].

LOGFILE
Log file name [import.log].

LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

MASTER_ONLY
Import just the master table and then stop the job [NO].

METRICS
Report additional job information to the import log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine data file references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].

SCHEMAS
List of schemas to import.

SERVICE_NAME
Name of an active service and associated resource group to constrain 
Oracle RAC resources.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_TABLESPACES
List of tablespaces from which metadata is loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name is appended
to the view name.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.

Include and Exclude

Include

impdp testdb/testdb schemas=apple include=TABLE:"IN ('test', 'emp')" 
directory=dumpdir  dumpfile=include.dmp logfile=include.log

Exclude

impdp testdb/testdb schemas=apple  exclude=TABLE:"= 'employee'" 
directory=dumpdir dumpfile=exclude.dmp logfile=exclude_im.log

Content {Meta_data_only / data_only}

Meta_data only

impdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_meta.dmp 
logfile=apple_meta01.log content=METADATA_ONLY

Data_only

impdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_data.dmp 
logfile=apple_data.log content=DATA_ONLY

Query

impdp system/password full=Y directory=dump_dir dumpfile=full.dmp logfile=full.log 
query='apple.EMP:"WHERE no=1",orange.DEPT:"WHERE no=2"'

Flashback_Scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3795360

impdp system/password directory=dumpdir dumpfile=flash.dmp logfile=flash.log
flashback_scn=3795360

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_IMPORT_SCHEMA_01 is:
  /home/oracle/backup/flash.dmp
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Aug 6 00:40:13 2021
elapsed 0 00:00:31

Flashback_Time

impdp directory=dumpdir dumpfile=flash_time.dmp logfile=flash_time.log’ 
tables=india.tamil 
flashback_time=\”to_timestamp(\’08-06-2021 00:48:00\’, \’DD-MM-YYYY HH24:MI:SS\’)\”

PARFILE

[oratest@oracle ~]$ cat > schema_par
directory=dump dumpfile=schema_par.dmp
logfile=schema.log
schemas=india
tables=apple

[oratest@oracle ~]$ impdp system/oracle PARFILE=schema_par

TABLES

SQL> create or replace directory dump_dir as '/home/oracle/backup';

Directory created.

[oracle@oracle ~]$ impdp  directory=dump_dir dumpfile=india.dmp logfile=india.log 
tables=india.apple
Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: test
Password:

Tablespace

impdp system/password 
directory=dump_dir 
dumpfile=tablespace.dmp 
logfile=tablespace.log 
tablespaces=system,india

Attach

The ATTACH parameter is used to attach the  interrupted job and status of data pump 
operation.

[oracle@oracle ~]$ impdp system/oracle attach=SYS_EXPORT_SCHEMA_01

Export: Release 19.0.0.0.0 - Production on Mon Aug 20 22:19:02 2018
Version 19.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM

Remap Table

[oracle@oracle ~]$ impdp DIRECTORY=dumpdir DUMPFILE=india_table.dmp 
logfile=india_table1.log TABLES=india.apple REMAP_TABLE=india.apple:mango

Username: system
Password:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "INDIA"."MANGO"                             5.609 KB       8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "INDIA"."SYS_IMPORT_TABLE_01" successfully completed at Wed Aug 4 21:49:48 2021 
elapsed 0 00:00:19

Remap_schema

[oracle@oracle ~]$ impdp directory=dumpdir dumpfile=india_owner.dmp 
logfile=india_owner1.log remap_schema=tamil:english

Username: system
Password:

. . imported "ENGLISH"."T1"                              5.539 KB       2 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:24:10 2021 
elapsed 0 00:00:17

Remap_Tablespace

[oracle@oracle backup]$ impdp  directory=dumpdir dumpfile=test_tablespace.dmp 
logfile=test_tablespace1.log remap_tablespace=test:orcl

Username: system
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dumpdir 
dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:56:25 2021 
elapsed 0 00:00:02

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Datapump Utilities Export Parameters

expdp help =y

[oracle@sample ~]$ expdp help=y

Export: Release 19.0.0.0.0 - Production on Thu Aug 5 21:33:52 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed
 within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: GROUP_PARTITION_TABLE_DATA, VERIFY_STREAM_FORMAT and 
XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 
and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [NO].

HELP
Display Help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].

LOGFILE
Specify log file name [export.log].

LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

METRICS
Report additional job information to the export log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

SAMPLE
Percentage of data to be exported. 

SCHEMAS
List of schemas to export [login schema].

SERVICE_NAME
Name of an active Service and associated resource group to constrain 
Oracle RAC resources.

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO]. 

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.

Full Database Export

[oracle@oracle backup]$  expdp system/password directory=dumpdir dumpfile=full.dmp 
logfile=full.log full=y



Master table "INDIA"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INDIA.SYS_EXPORT_FULL_01 is:
  /home/oracle/backup/full.dmp
Job "INDIA"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 6 00:19:43 2021 
elapsed 0 00:02:41

Include and Exclude

Include

expdp testdb/testdb schemas=apple include=TABLE:"IN ('test', 'emp')" 
directory=dumpdir dumpfile=include.dmp logfile=include.log

Exclude

expdp testdb/testdb schemas=apple  exclude=TABLE:"= 'employee'" 
directory=dumpdir dumpfile=exclude.dmp logfile=exclude.log

Content {Meta_data_only / data_only}

Meta_data only

expdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_meta.dmp 
logfile=apple_meta.log content=METADATA_ONLY

Data_only

expdp system/password schemas=apple  directory=dump_dir  dumpfile=apple_data.dmp 
logfile=apple_data.log content=DATA_ONLY

Query

expdp system/password full=Y directory=dump_dir dumpfile=full.dmp logfile=full.log 
query='apple.EMP:"WHERE no=1",orange.DEPT:"WHERE no=2"'

Flashback_Scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3795360


expdp system/password directory=dumpdir dumpfile=flash.dmp logfile=flash.log 
flashback_scn=3795360

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/flash.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 6 00:40:13 2021 
elapsed 0 00:00:31

Flashback_Time

expdp directory=dumpdir 
dumpfile=flash_time.dmp 
logfile=flash_time.log' 
tables=india.tamil 
flashback_time=\"to_timestamp(\'08-06-2021 00:48:00\', \'DD-MM-YYYY HH24:MI:SS\')\"

PARFILE

[oratest@oracle ~]$ cat > schema_par
directory=dump dumpfile=schema_par.dmp
logfile=schema.log
schemas=india
tables=apple

[oratest@oracle ~]$ expdp system/oracle PARFILE=schema_par

TABLES

SQL> create or replace directory dump_dir as '/home/oracle/backup';

Directory created.

[oracle@oracle ~]$ expdp  directory=dump_dir dumpfile=india.dmp logfile=india.log 
tables=india.apple

Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: test
Password:

Tablespace

exp system/password directory=dump_dir dumpfile=tablespace.dmp logfile=tablespace.log 
tablespaces=system,india

Attach

The ATTACH parameter is used to attach the  interrupted job and 
status of data pump operation.

[oracle@oracle ~]$ expdp system/oracle attach=SYS_EXPORT_SCHEMA_01

Export: Release 19.0.0.0.0 - Production on Mon Aug 6 22:19:02 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM

Transport_Tablespaces

Use the TRANSPORT_TABLESPACES  parameter to specify a list of tablespace names for 
which object metadata will be exported from the source database into the 
target database.

Keep INDIA Tablespace in Read only mode.

SQL> alter tablespace INDIA read only;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/india01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf

[oracle@oracle]$ scp /u01/app/oracle/oradata/proddb/india01.dbf 
[email protected]/u01/app/oracle/oradata/TEST/datafile

[oracle@oracle export]$ expdp DUMPFILE=india.dmp  LOGFILE=india.logDIRECTORY=dumpdir 
TRANSPORT_TABLESPACES=india 

Export: Release 19.0.0.0.0 - Production on Thu Aug 6 23:07:59 2018
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Username: system
Password:

******************************************************************************
Datafiles required for transportable tablespace INDIA:
/u01/app/oracle/oradata/proddb/india01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu 
Aug 6 23:08:48 2021

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Export and Import Utilities

Export(exp) and import(imp) are Logical backup and Recovery. When exporting the database objects are dumped to a binary file which can then be imported into another Oracle database.

The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. First, let us see export in detail.

Difference between exp/imp and expdp/impdp

Exp/imp is a Traditional  export import,expdp/impdp is a Datapump export    
  import.

Traditional export import utility is starting with exp/imp,Datapump utility   
is starting with expdp/impdp. 

Datapump access files in the server (using ORACLE directories). Traditional export can 
access files in client and server both (not using ORACLE directories).

exp/imp (Traditional)  is using conventional path , expdp /impdb (Datapump) is using 
Direct path.

Exp (Traditional) is byte mode ,Datapump is block mode.

Data Pump will recreate the user, whereas the old imp utility required the DBA to
create the user ID before importing.

Datapump utility we can stop and Restart the Jobs.

Features of Datapump utility

Job Estimation can be Done in Datapump.

Data Remapping can be done using REMAPDATA parameter.

EXCLUDE and INCLUDE parameter allows the fine-grained object selection.

Failed export/import Jobs can be Restarted.

Export and import can be taken over the network using database links even 
without Generating the dump file using NETWORK_LINK parameter.

CONTENT parameter gives the freedom for what to export with options METADATA ONLY,
DATA, BOTH.

You don’t need to specify the BUFFER size in datapump

Job estimated completion time can be monitored from v$session_longops view.

Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you
have to compress the dumps using OS utilities.

Data encryption can be done in datapump.

DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.

REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SQLFILE Parameter in Oracle19c Database.

DESCRIPTION:

In this article we are going to see the Oracle 19c Datapump- Sqlfile

Sqlfile option will show you the DDL in the text format.Sometimes customer provides us data pump export file, by that time we will use SQLFILE parameter and find out DDL’s (Schema names) and perform data pump import accordingly.

SYNTAX:

impdp directory=DATA_PUMP_DIR dumpfile=PRODDB_Full.dmp sqlfile=FULL_DDL.sql

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01' sqlfile=tab.sql

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:30:30 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "DATA"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded

Starting "DATA"."SYS_SQL_FILE_TABLE_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01 sqlfile=tab.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_SQL_FILE_TABLE_01" successfully completed at Mon Jan 25 09:30:36 2021 elapsed 0 00:00:01




[oracle@oracle19c ~]$ cd /u01/app/oracle/oradata/

[oracle@oracle19c oradata]$ ls

compressed1.dmp  con01.dmp  con.log        CONTINUE.dmp  My_Dir        schema.dmp  table01.dmp       tablespace.log  tabs.log

compressed2.dmp  con01.log  CONTINUE1.dmp  CONTINUE.log  ORACLE19C     schema.log  table01.log       tabs00.log      tab.sql

compressed.dmp   con.dmp    CONTINUE1.log  export.log    ORACLE19CCON  tab00.dmp   tablespace01.log  tabs.dmp

sqlfile activity completed successfully and ‘tab.sql‘ file has created in the specified dump directory location.

[oracle@oracle19c oradata]$ vi tab.sql

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

TABLESPACE LEVEL PARAMETER

DESCRIPTION:

In this article, we are going to see how to export and Import table space using Datapump.

Tablespaces are the logical storage units which are used by the database to store separate objects, such as tables, types, PL/SQL code, and so on. Typically, related objects are grouped together and stored in the same tablespace.

Using expdp export utility of data pump we can export tablespaces. Exporting tablespace is also a way of taking logical backup of the tablespace of your database. Exporting tablespace means only the tables contained in a specified set of tablespace are unloaded along with its dependent objects.

Exporting tablespace means

Only the tables contained in a specified set of tablespace are unloaded
If a table is unloaded, then its dependent objects are also unloaded
Tablespace export unloads both object metadata and Data.

DIRECTORY.

Create a directory anywhere in your system or on your network where expdp export utility can save the exported files such as dump files and log files.

TABLE SPACE:

Using this TABLESPACES parameter we can specify the list of tablespace names which you want to export. For example here I have specified USERS.

[oracle@oracle19c ~]$ expdp directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:22:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_TABLESPACE_01":  data/******** directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_TABLESPACE_01 is:

  /u01/app/oracle/oradata/tab00.dmp

Job "DATA"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jan 25 09:22:51 2021 elapsed 0 00:00:09

Export activity completed successfully.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:23:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

SQL> drop table table01;

Table dropped.

SQL> select * from Table01;

select * from Table01

              *

ERROR at line 1:

ORA-00942: table or view does not exist


SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Import the tablespaces using the Dumpfile

[oracle@oracle19c ~]$ impdp directory=My_Dir  dumpfile=tab00.dmp  logfile=tabs00.log

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:28 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 09:24:36 2021 elapsed 0 00:00:03

Import Activity successfully completed.Now we can access the tablespaces.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:51 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.


SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

Schema Level Export & Import using Datapump in Oracle19c

DESCRIPTION:

In this tutorial we going to learn how to export & Import schema using expdp & Impdp using data pump in Oracle19cDatabase.

A schema is a collection of a logical structure of data or, database objects owned by a database user and shares the same name as the user.Using expdp export utility you can export any schema of your database or we can also say that using expdp export data pump we can take logical backup of any schema in Oracle database.

DIRECTORY:

create a directory object you can use CREATE DIRECTORY command.

Grant DATAPUMP_EXP_FULL_DATABASE role:

Apart from granting read and write privilege on the directory to the user we also need to grant DATAPUMP_EXP_FULL_DATABASE role to the user who wants to perform the export.

SCHEMA:

DATA

In below mentioned query we are going to export schemas at the values of SCHEMA parameter,

[oracle@oracle19c ~]$ expdp data/pump schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 07:02:14 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_SCHEMA_01":  data/******** schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Master table "DATA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/oradata/schema.dmp

Job "DATA"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jan 25 07:04:22 2021 elapsed 0 00:01:48

Export utility successfully completed.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 07:08:24 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0.

SQL> drop user data;

User dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Now we are going to see IMPORT activity.

Performing the import of Schemas of your database is as simple as exporting them. Similar to expdp Schema export, we use SCHEMAS parameter to perform the import of the schema. SCHEMAS parameter specifies that user wants to perform Schema-Mode import. Also using Schema parameter helps you in choosing a particular schema from the multiple schema export to import

Executing this impdp data pump import command will import the schema DATA into your database.

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 07:09:09 2021

Version 19.3.0.0.0

Username: system

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 07:09:33 2021 elapsed 0 00:00:05

Import action completed succesfully.Now we can connect to the schemas.

[oracle@oracle19c ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jan 25 07:09:38 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 SQL> conn data/pump; Connected. Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Full Database Export in Oracle19c using Datapump

DESCRIPTION:

Using expdp utility provided by data pump we can export data or say unload data or metadata from one database to another. By using this utility we can either export the complete database or a subset of databases such as Database Schema, Table spaces or even individual tables.

DATABASE VERSION:

19.3.0.0.

DIRECTORY:

create a directory by using CREATE DIRECTORY command.

FULL:

This parameter FULL indicates that you want to perform a full database export. This parameter can have YES or NO values. If you set this parameter to YES that means expdp utility will export all the data and metadata of the database.

DUMPFILE:

Using DUMPFILE parameter you specify the names, and optionally, the directory objects of dump files for an export job.

[oracle@oracle19c ~]$ expdp directory=My_Dir  FULL=Y  dumpfile=fulldb.dmp logfile=fulldb.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:35:42 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_FULL_01":  data/******** directory=My_Dir FULL=Y dumpfile=fulldb.dmp logfile=fulldb.log

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/RADM_FPTM

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.070 KB      37 rows

. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181"           136.5 KB     174 rows

. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows

. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows

. . exported "WMSYS"."WM$HINT_TABLE$"                    9.984 KB      97 rows

. . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows

. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.078 KB      11 rows

. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows

. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows

. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows

. . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows

. . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows

. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows

. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows

. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows

. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows

. . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows

. . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows

. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows

. . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows

. . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows

. . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows

. . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows

. . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows

. . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows

. . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows

. . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows

. . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows

. . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows

. . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows

. . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows

. . exported "SYS"."AUD$"                                    0 KB       0 rows

. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows

. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows

. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows

. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows

. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows

. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows

. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows

. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows

. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows

. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows

. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows

. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows

. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows

. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows

. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows

. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows

. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows

. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows

. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows

. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows

. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows

. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows

. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows

. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows

. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows

. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows

. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows

. . exported "MDSYS"."RDF_PARAM$"                        6.515 KB       3 rows

. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows

. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows

. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows

. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows

. . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows

. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows

. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows

. . exported "SYS"."SQL$TEXT_DATAPUMP"                   362.5 KB      63 rows

. . exported "SYS"."SQL$_DATAPUMP"                       7.945 KB      63 rows

. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"             72.75 KB      94 rows

. . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows

. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                1.847 MB    3100 rows

. . exported "SYS"."SQLOBJ$_DATAPUMP"                    14.69 KB      94 rows

. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows

. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"               0 KB       0 rows

. . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows

. . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_FULL_01 is:

  /u01/app/oracle/oradata/fulldb.dmp

Job "DATA"."SYS_EXPORT_FULL_01" successfully completed at Mon Jan 25 09:38:24 2021 elapsed 0 00:02:38

full export is done successfully.

Restrictions with Full Database export.

A full export does not export system schemas that contain Oracle-managed data and metadata.
Grants on objects owned by the SYS schema are never exported.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Table Level Export & Import in Oracle 19c using Data pump.

DESCRIPTION:

In this Blog, we will learn how to use the Oracle Data Pump Export and Import utility in Oracle 19c database

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, table space, or table-mode export dump file set or another database.

Create a table with an encrypted column and insert rows:

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

Before start the Export Process, we need to create a Directory which is used to store the dump file.Data Pump is a server-based technology, so it typically deals with directory objects pointing to physical directories on the database server.

Dumpfile: The name of the exported dump file.

Logfile: The name of the directory object that is used to store the log file of the export operation, i.e. the name of the log file

Export the dump file of the table:

Directory Name:MY_Dir

Table Name: Table01

[oracle@oracle19c ~]$ expdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01';

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 08:39:53 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_TABLE_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/oradata/table01.dmp

Job "DATA"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 25 08:40:05 2021 elapsed 0 00:00:08

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:40:09 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 SQL> conn data/pump

Connected.

Drop the table: 

SQL> drop table table01;

 Table dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:41:28 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

The Data Pump Import utility is started using the impdp command

The following example is a command to import Tables using Dump file.

Dumpfile:table01.dmp

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log;

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:02 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 08:42:21 2021 elapsed 0 00:00:14

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:27 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

Finally, verify the contents of the Imported table:

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1                   aaa

 2                   bbb

 3                   ccc

 4                  ddd

 5                  eee

Category Archives: Datapump

DATA GUARD CONFIGURATION PARAMETERS

Data Guard Architecture

Include and Exclude

Include

Exclude

Content {Meta_data_only / data_only}

Meta_data only

Data_only

Query

Flashback_Scn

Flashback_Time

PARFILE

TABLES

Tablespace

Attach

Remap Table

Remap_schema

Remap_Tablespace

expdp help =y

Full Database Export

Include and Exclude

Include

Exclude

Content {Meta_data_only / data_only}

Meta_data only

Data_only

Query

Flashback_Scn

Flashback_Time

PARFILE

TABLES

Tablespace

Attach

Directory

Transport_Tablespaces

Difference between exp/imp and expdp/impdp

Features of Datapump utility

DESCRIPTION:

SYNTAX:

DESCRIPTION:

DIRECTORY.

TABLE SPACE:

Import the tablespaces using the Dumpfile

DESCRIPTION:

DIRECTORY:

Grant DATAPUMP_EXP_FULL_DATABASE role:

SCHEMA:

DESCRIPTION:

DATABASE VERSION:

DIRECTORY:

FULL:

DUMPFILE:

DESCRIPTION:

Create a table with an encrypted column and insert rows:

Export the dump file of the table:

Finally, verify the contents of the Imported table: