Export-Import Using Unix Pipe

In scenarios where there is no disk space to hold the export dumps or when migrating schemas across different versions of Oracle, this script helps to do the export/import over the network or on the same server using the Unix pipe.

In this example, data is exported from the Oracle Database instance named PROD to the instance named DEV. The export utility connects to the PROD database using the Net Service name, writes the export dump to a Unix pipe, which is read by the import process that loads the data to the DEV database.

Create a file exp_imp.sh:


export ORACLE_HOME=/oracle/product/10204

# make a unix pipe
export PIPE=exp_imp_pipe
mknod exp_imp_pipe p

# Start the Export and run it in background  - Dont omit the '&' at the end.
exp scott/tiger@PROD file=exp_imp_pipe log=exp_PROD.log buffer=1024000 consistent=y OWNER=HR,APP direct=y statistics=none compress=n  &

# Start the Import
imp scott/tiger ignore=y full=y file=exp_imp_pipe log=imp_DEV.log commit=y buffer=1024000 RESUMABLE=Y &

For big schemas, CONSISTENT=Y may not work as the export may fail with an ORA-01555 snapshot too old error, so you may need to revist the parameter. RESUMABLE=Y at the import side can help to make the session ‘resumable’ in case the import hits any space issues on the target database. The export/import logs can be monitored to view the progress of the activity.

In this example the user scott has enough privilege to exp/imp the data, make sure that you connect to the databases as a privileged user and needless to say, it is recommended to run the job in nohup mode to avoid issues in case connectivity to the server gets disturbed.


Database/Tablespace Free space in MB

One of the most used script used by the DBA in day-to-day work, this script gives the space usage of each tablespace in the database.

set heading on
set pagesize 500
set lines 400

column tablespace       format a30                heading "Tablespace"
column avail            format 9,999,999,999,999  heading "MB Avail."
column used             format 9,999,999,999,999  heading "MB Used"
column free             format 9,999,999,999,999  heading "MB Free"
column pct              format 999                heading "Pct"

compute sum of avail used free on report
break on report
select  a.tablespace_name "Tablespace",
        a.avail-b.free used,
        round(nvl((a.avail-b.free)/a.avail*100,0))      "Pct"
(select tablespace_name, round(sum(bytes)/1048576)     avail
        from    sys.dba_data_files
        group by tablespace_name
        select  tablespace_name,round(sum(bytes_free+bytes_used)/1048576)
        from v$temp_space_header
        group by tablespace_name)       a,
(select tablespace_name, round(sum(bytes)/1048576)     free
        from    sys.dba_free_space
        group by tablespace_name
        select  tablespace_name,round(sum(bytes_free)/1048576)
        from v$temp_space_header
        group by tablespace_name)       b
where  a.tablespace_name = b.tablespace_name (+);

Hourly/Daily Archive generation

The below query comes handy to understand the archivelog generation of an Oracle database on an hourly /daily basis, per thread – in case of RAC databases.

Archivelog generation on a daily basis:

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Archive log generation on an hourly basis:

set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Sample output:

HOUR                   THREAD#         MB   ARCHIVES
------------------- ---------- ---------- ----------
2009 08 20 12:00:00          1      31268        339
2009 08 20 13:00:00          1       4994         55
2009 08 20 14:00:00          1       4412         48
2009 08 20 15:00:00          1       4805         52
2009 08 20 16:00:00          1       3364         37
2009 08 20 17:00:00          1         22          1
2009 08 20 21:00:00          1          9          1

Also, the following script is useful to find the archivelog switches on an hourly basis that happened in the past one week, I got this from http://kubilaykara.blogspot.com/2008/02/redo-log-generation.html and is quite an useful one.

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
where to_date(first_time) > sysdate -8
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)

Sample output:

DAY 00 01 02 03 04 05 06 09 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -
01-NOV-09 0 1 1 0 1 0 1 0 1 4 1 1 0 1 0 1 1 0 1 1 1 1 1 1
02-NOV-09 0 1 1 1 1 0 1 1 1 1 0 1 1 7 1 1 1 1 1 2 1 1 1 1
03-NOV-09 1 2 2 1 1 1 1 1 1 1 1 2 1 1 1 1 1 2 1 1 2 1 1 2
04-NOV-09 1 1 8 1 7 2 1 1 1 2 1 1 2 1 2 1 2 1 2 1 2 1 2 2
05-NOV-09 2 1 2 1 2 2 1 2 1 2 2 1 2 2 1 2 2 2 1 2 2 2 2 2
06-NOV-09 2 1 2 2 2 2 1 2 2 2 2 2 1 2 2 1 0 2 0 0 0 0 1 0
09-NOV-09 0 0 1 7 0 0 1 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0

Data guard setup and configuration – quick reference

Quick reference to dataguard configuration for the DBAs. The table gives an easy reference on the main attributes of log_archive_dest_n parameter and gives quick concept on the setting. Also, the scenarios where standby redo logs are used to minimize data loss and also to enable real-time apply is discussed.

Configuration Performance Availability Protection Meaning and Remarks
Standby redo logs Not required, but recommended Required Required Redo data from primary will be written to standby redo logs by LGWR process and real time apply can be enabled, LGWR/ARCH parameter can be set.
(Redo archival process)
Not required Required Required Specifies the redo transport service uses LGWR to collect and transit redo data to standby.
(Redo archival process)
Possible Not possible Not possible Specifies that redo transport services uses ARCn process to collect and transmit redo data to standby.
SYNC (Network transmission) Not required Required Required SYNC specifies Network I/O to standby is synchronous, that means the LGWR process on primary will wait for Network I/O to complete on the standby so that successful transfer of redo records to standby database is ensured.
ASYNC (Network transmission) Can be set Cannot be set Cannot be set ASYNC specifies the the LGWR will not wait for Network I/O  to complete and proceeds asynchronously. Not valid if ARCH parameter is used
(Disk Writes)
Not required Required Required Specifies that disk I/O to archived redo logs and standby redo logs on the standby are done synchronously and the LGWR process on primary will wait to continue its processing.
NOAFFIRM (Disk Writes) Can be set Cannot be set Cannot be set Specifies that disk I/O to archived redo logs and standby redo logs on the standby is done asynchronously and the LGWR process on primary will not wait to continue its processing.



Oracle 11g Standby database creation on Unix using RMAN Active database duplication.

Step by Step

1) Activate Force logging on the primary database

In case of unlogged writes to primary database (NOLOGGING  and DIRECT LOAD operations using SQL*Loader),  redo data is not generated.  As the redo required to synch the changes to the standby is not generated, the standby database will not be consistent with the primary.  To prevent such issues, the primary database is forced to log operations. It may also be noted that it is recommended to take a backup of the database after UNRECOVERABLE/NOLOGGING operations as those operations are not recoverable as the redo data is not generated.Use the below command on the primary database to force logging on the database.

SQL> alter database force logging;

This must be done before building the standby database.

2) Prepare Initialization files for the primary and standby databases

# Initialization file for primary database – initorcl.ora

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

##### Standby related parameters ######## 

*.LOG_FILE_NAME_CONVERT=('/oradata/stby/','/oradata/orcl/') *.log_archive_config='dg_config=(orcl,stby)'
*.log_archive_dest_2='SERVICE=STBY valid_for=online_logfiles,primary_role)'

##### End Standby related parameters #######

# Initialization file for standby database - initstby.ora 


##### Standby related parameters#######

*.log_archive_dest_2='SERVICE=ORCL valid_for=(online_logfile,primary_role)

##### End Standby related parameters #######

3) Configure Listener for Static service registration

Static service registrations is requierd for the RMAN active database duplication to work.

(ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521))))  SID_LIST_LISTENER =
(SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/11.1.0) (SID_NAME = orcl))
(SID_DESC = (ORACLE_HOME = /oracle/app/oracle/product/11.1.0) (SID_NAME = stby))

4) Create service names in tnsnames.ora
Add tnsnames.ora entries for primary and standby databases.

(ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521))
(SERVICE_NAME = orcl)))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbapundits)(PORT = 1521))
(SERVICE_NAME = stby)))

5) Create password file for the standby database

The easiest way to do this is to copy the password file from the primary database and to rename it in proper format on the standby server.

$ pwd
$ cp orapworcl orapwstby 

6) Perform Oracle 11g standby creation using RMAN DUPLICATE command

duplicate target database for standby dorecover from active database;

The primary database is duplicated as a standby database on the target server (or on same server in this case) using the datafiles from the active database. There is no need of a backup and the datafiles are copied over the network to the target server and is recovered to the point when the duplication was fired.

7) Logs

11g-standby-database-creation-with-rman-duplicate-command – log


Oracle 11g active database duplication on Unix/Windows

In this post, let me take you through the step-by-step method of active database duplication, a new feature of Oracle 11g to create a duplicate database. Active database duplication can also be used to create a standby database, which is mentioned separately in another post.


Upto Oracle version 11g, RMAN ‘DUPLICATE’ command required a database backup for performing database duplication/cloning. This is called backup based database duplication. Starting from Oracle 11g, an active database can be cloned to the same host or another without using an RMAN backup and the recovery catalog and this is called active database duplication.This can also be used to create a physical standby database. RMAN copies the live database files over the network to create the duplicate/standby database.


* The source database must be in ARCHIVELOG mode if it is open.
* If the source database is in NOARCHIVELOG mode, it should be mounted after a clean shut down.
* Connections to the auxiliary instance must be through a Net service name.
* The auxiliary instance must have a password file created, the password for the SYSDBA user used for the activity must be the same.

Practical issues and solutions

When the source database server and the target database server follow the same filesystem structure,size and standards, the duplication works fine without any issues. This may not be the case in most of the environments. The source database will have files laid across the mountpoints (say) ‘/oracle/oradata1/PROD’, ‘/oracle/oradata2/PROD’ etc. The target server may follow a totally different structure. In such cases, perform any of the following methods:

1) Use the ‘SET NEWNAME FOR DATAFILE’ and ‘SET NEW NAME FOR TEMPFILE’ in the run {} block while performing restore/duplication. When specified,  the ‘SET NEWNAME’  command will take precedence over the other methods of renaming the database files.

If the destination server follows a different filesystem layout and if the files needs to be restored to a separate set of directories different than that from the source database, ‘SET NEWNAME’ command needs to be used for each datafile and tempfile (‘SET NEW NAME FOR TEMPFILE’ command is introduced in Oracle 11g)


If ‘SET NEWNAME’ commands are used in a ‘run {}’  block while performing a restore operation, the ‘run {}’ block must contain the command ‘SWITCH DATAFILE ALL;’  and ‘SWITCH TEMPFILE ALL;’  (SWITCH TEMPFILE ALL; is available starting from Oracle 11g). If the ‘SWITCH; command is missed out during a restore operation,  ‘ALTER DATABASE RENAME FILE’  command must be used for each datafile before proceeding with the recovery. The details required to perform the manual execution of ‘ALTER DATABASE RENAME FILE’ can be found from the alert log. It is a tedious task to do so, so NEVER forget to add the SWITCH commands in the run block  if you are performing an RMAN restore. For database duplication using DUPLICATE command, ‘SWITCH DATAFILE/TEMPFILE’ commands are not required as this function is handled by RMAN automatically.

2) DB_FILE_NAME_CONVERT clause in the DUPLICATE statement. This method will have the precedence if the DB_FILE_NAME_CONVERT / DB_LOG_FILE_NAME_CONVERT parameters are used in the Init.ora file.

3) DB_FILE_NAME_CONVERT and DB_LOG_FILE_NAME_CONVERT parameters in Init.ora.

4) In case Oracle managed files are used in the source database, either you can specify ‘SET NEWNAME’ commands for each datafile and/or specify the parameters DB_FILE_CREATE_DEST and DB_CREATE_ONLINE_LOG_DEST_n in the auxiliary instance to get the datafiles, temp files and redo logs created in the respective directories.


Terms used to describe the method step by step:

Target: Database to be cloned. (Source database – ORCL instance)
Auxiliary: Destination database to which the source database will be cloned (ORCLDUP instance)

This scenario explains that the source and target database instances are on a Windows server. The method is the same for database duplication across 2 different servers (on Unix also) except that some of the OS commands mentioned below may need to be changed.

1) Prepare Init.ora file for the auxiliary instance

Copy the Init.ora file from the source server to the target server, to the dbs/database directory based on the underlying platform. Make necessary modifications in the Init.ora file, mainly the below parameters:


2) Create Password file

Copy the password file to the target server, rename it in the below format, this is the easiest method; you can always create the same with orapwd utility.

Unix Servers: orapw<SID><sid>.ora
Windows Servers: PWD<SID><sid>.ora

3) Create Oracle related services (Required for Oracle on Windows only)

Use Oradim utility to create an Oracle service on Windows servers., this step is not applicable for Unix servers.

oradim -new -sid orcldup -srvc oracleserviceorcldup

4) Create directories for database files

Create the required directories on the target server for datafiles, redo logs, control files, temporary files etc, this example assumes that all the database files will be stored under ‘C:ORACLEORADATAORCLDUP’

5) Configure listener to user static service registration

Static service registration for the listener must be done on the target server, this is because the database instance in NOMOUNT state (auxiliary instance before cloning) will not allow database connections, it will be BLOCKED. To configure static service registration with the listener, update the listener.ora file as below. Please note that, in this example the target and auxiliary instances are on the same server.

(ORACLE_HOME = C:\oracle\product\11.1.0\db_1)
(SID_DESC = (ORACLE_HOME = C:oracle\product\11.1.0\db_1)

6) Reload the listener

lsnrctl reload;

7) Start and verify the auxiliary instance

Start the auxiliary instance; verify the parameters db_name and control_files.

SQL> show parameter db_name;
SQL> show parameter control_files;

8) Perform Oracle 11g Active database duplication

run {
duplicate target database to orcldup from active database;

9) Logs

Oracle 11g Active database duplication Log

10) References


Oracle documentation on RMAN database duplication


Welcome to DBAPundits.com

Welcome to my blog on Oracle database administration, this is my first blog that got started on 28th Dec, 2008.

The term “Pundit” derived from Sanskrit means a learned person/expert/authority in a particular subject. This website is powered by industry experts with solid skills and experience in database administration.

DBAPundits.com aims to be a Wiki for the topics related to Oracle database administration and provides basic to expert level of information for each topic.
Hope you will find DBAPundits.com a useful reference soon.