BACKUP & RECOVERY

How to duplicate database using RMAN backup set

Though we can the active duplication method, to clone a database from live running database. But sometime we may need to clone a database from a RMAN backup set. Below example will cover the steps for that. STEPS: 1. Take full rman backup from source db If you already have any existing latest valid full […]

Useful flashback related commands

Below are the collection of useful flashback related commands. 1. How to check whether flashback is enabled or not: select flashback_on from v$database; 2. Enable flashback in database: — make sure database is in archivelog mode: alter system set db_recovery_file_dest_size=10G scope=both; alter system set db_recovery_file_dest=’/dumparea/FRA/B2PMT3′ scope=both; alter database flashback on; 3. Disable flashback in database: […]

COMPRESSION in datapump oracle

COMPRESSION parameter is used with EXPDP, to compress the generated dump file. NOTE – For using compression parameter with datapump, we need to have Oracle advance compression license.   COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. In this below demo, we will compare the dump […]

REUSE_DUMPFILES parameter in EXPDP

If we try to export a dumpfile with the name, which is already present in that directory. then we will get error like ORA-27038: created file already exists ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file “/export/home/oracle/dbaclass_estim.dmp“ ORA-27038: created file already exists Additional information: 1 So if the […]

SAMPLE parameter in EXPDP to export subset of data

Let’s say we have a very big table, and for testing purpose, we need only a subset of data i.e to let’s say 10 percent of data from that table, then SAMPLE parameter can be used in EXPDP. We will explain its use in below demo. SAMPLE Percentage of data to be exported. DEMO: SQL> […]

ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only

ESTIMATE_ONLY parameter can be used in EXPDP command, to estimate the disk space required for the export job, without doing the actual export. So when we are not sure what will be the export dump size, then better to use this option first, before generating the export dump. By default ESTIMATE_OPTION=N DEMO: PARFILE WITH ESTIMATE […]

FLASHBACK parameter in DATAPUMP(EXPDP)

We can use the flashback parameter ( FLASHBACK_SCN or FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of the table as of a specific point in time. Flashback method depends upon undo data. So if we are trying to take an export of the table with the flashback for which data is not available in […]

CONTENT parameter in datapump

CONTENT parameter is used in expdp or impdp, to load/unload data only, metadata only or both. CONTENT Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.   CONTENT=ALL is the default value CONTENT=METADATA_ONLY: It will take export only the metadata, i.e skeleton/DDL on the object/schema. It won’t export any of the data/rows. […]

Shell script to delete old archives using RMAN

If the requirement is to delete archive log backups automatically (without taking backup), then below shell script can be configured in crontab. prepare the shell script. cat rman_arch_del.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2.0 export ORACLE_SID=PARIS12C export PATH=$ORACLE_HOME/bin:$PATH delBackup () { rman log=/home/oracle/arch_del.log << EOF connect target / DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’; CROSSCHECK ARCHIVELOG ALL; […]

Query clause in oracle datapump expdp

             QUERY clause can be used in expdp or impdp to export/import subset of the data or data with specific conditions. DEMO: Export dump of a table from emp_tab WHERE created > sysdate -40 . Filter can be added on any column depending upon the requirement. SQL> select count(*) from […]