DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS is a very useful parameter mostly used with table_exists_action=APPEND . In general, if you are appending data ( table_exists_action=APPEND) to an existing table with a unique constraint and if the new rows violating the unique constraint, then the import job will fail( even if only one row violates the constraint) DATA_OPTION=SKIP_CONSTRAINT_ERRORS parameter will help […]

It can be used, only with IMPDP. This helps in generating the DDLs from a dumpfile. Suppose I have dump file of table DBACLASS.DEP_TAB . If you need the DDL of the table, then use SQLFILE with IMPDP command as below. This will not do the actual import. It will just dump the metadata (DDL) […]

TABLE_EXISTS_ACTION parameter is required while importing table, which is already present in the database. We can put required value, according to our requirement.   TABLE_EXISTS_ACTION Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.   TABLE_EXISTS_ACTION=APPEND: With this option, while importing the table, if the table exists in […]

We can enable password based encryption for expdp dumps, by using different encryption related parameters in expdp/impdp command. ENCRYPTION RELATED PARAMETERS: 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. […]

In a RAC database, if you are taking export with the parallel option and the datapump directory is not shared between the nodes, then export might fail with below error Because the remote node is unable to write the dumps to that directory. ORA-31617: unable to open dump file “/dumparea/TEST/asset_75.dmp” for write ORA-19505: failed to […]

It is tvery common to export dump file to the standard file system(Mount points). However, we can export the dump files to ASM diskgroup also. In this article, we will show the steps for this . DEMO 1.Create a directory pointing to ASM diskgroup( for dump files) SQL> create directory SOURCE_DUMP as ‘+NEWTST/TESTDB2/TEMPFILE’; Directory created. […]

By using network_link parameter, we can directly import the data from source database to target database without generating the dump file. This will need a database link between source and target db . Lets’ see the below example. DEMO: Objective – Export table dbatest.EMP_TAB from SOURCEDB and import in TARGETDB using network link. 1. Add […]

In the multitenant database, For getting an export dump from the pluggable database, we need a follow a bit different process. DEMO: PLUGGABLE DATABASE(PDB) – PRE1 SCHEMA_NAME – DBACLASS ( this schema/user is present in PDB PRE1) 1. Make sure PDB service is registered in the listener. $ lsnrctl status LISTENER_POC LSNRCTL for Solaris: Version […]

In this article, we will duplicate the database(with new db_name) from tape backup, without connecting to source database. We will only connect to the recovery catalog and auxiliary instance. SOURCE DB – PRODDB AUXILIARY DB – TESTDB CATALOG DB – ORACAT I.e we will duplicat the database with db_name TESTDB from rman tape backup of […]

Below are the steps for changing the flash recovery area(FRA destination). 1. Check the current FRA destination. SQL> show parameter db_recovery_file NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /dbaclassdb/oradata/FRA db_recovery_file_dest_size big integer 20G SQL> select name from V$RECOVERY_FILE_DEST; NAME ————————————– /dbaclassdb/oradata/FRA 2. Change db_recovery_file_dest parameter. SQL> alter system set db_recovery_file_dest=’/fradg/oradata/FRA’ scope=both; System altered. select […]