Long Live Data Pump!

Oracle DBAs would love to reminisce about export and import utilities. The primary usage of export was to perform logical backup of the database, and then import the dump file to recreate the database on the same OS platform or different OS platform. Quite useful! In addition, Oracle DBAs routinely extracted DDLs and user creation scripts off of export dump file. Few third-party tools and utilities were specifically designed to extract DDLs off of the dump file. In fact, export backup did save my day when I had to extract hashed passwords for few users whose passwords were accidently changed.

As of Oracle11g, Export/Import has been desupported and given way to much more versatile Data Pump utility. Oracle describes Data Pump as very high-speed data movement utility. Indeed, Data Pump is quite powerful and DBAs should add data pump backups to Database Disaster/Recovery Plan.

The purpose of this blog is to examplify few of its indirect usages that will come quite handy. For example, you may ask, “Can I extract hashed passwords with data pump?” The answer is, “Of course!” Let’s discuss.

Extract Hashed Passwords

To extract hashed passwords, all you need to do is run data pump import with SQLFILE and INCLUDE parameters, as illustrated below:

C:\Oracle\oraback\jdev11g>impdp system/****** directory=dp_backup dumpfile=jdev11g.dmp logfile=jdev11gimp.log include=USER sqlfile=jdev11g.sql

Import: Release 11.2.0.1.0 - Production on Mon Sep 27 21:58:04 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=jdev11g_1.dmp directory=dp_backup include=USER logfile=jdev11gimp.log sqlfile=jdev 11g.sql content=metadata_only Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type

DATABASE_EXPORT/SCHEMA/USER Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:10

--- Contents from SQLFILE ---

CREATE USER "JMEHTA" IDENTIFIED

BY VALUES 'hash string'     

DEFAULT TABLESPACE "USERS"  

TEMPORARY TABLESPACE "TEMP";

SQLFILE parameter directs data pump import (impdp) to write DDLs to the file specified with this parameter. Writing DDLs to the SQLFILE is “instead of behavior.” Impdp doesn’t execute the DDLs in the database. Impdp needs a database connection to execute DBMS_METADATA and DBMS_DATAPUMP PL/SQL packages to perform its work.

Also note that with SQLFILE parameter, you cannot specify CONTENT=ALL or DATA_ONLY. In other words, impdp doesn’t generate SQL statements that could be used later to populate the database tables. SQLFILE includes DDLs for tablespaces, users, role grants, packages, procedures, functions, tables, indexes, primary and foreign keys, etc.

INCLUDE parameter allows to target the DDLs you are interested in. Specifying INCLUDE=USER will give you CREATE USER statements. To see a list of valid paths for use with the INCLUDE parameter, you can query the following views: DATABASE_EXPORT_OBJECTS for Full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode.

Cloning an Oracle 10g Database on a New Server

In the previous blog, I explained how to clone an Oracle 10g Database on the same server using RMAN. As explained in the previous blog, cloning an Oracle database on the same server contends for the resources with the primary database, and it may not work for many of you. Your only option is to clone the database on a different server.

Whether you are planning to clone an Oracle database on the same server or different server, the RMAN procedure is pretty much the same with the exception of few additional steps that you need to perform. All steps are described below.

The requirement for this task is similar – clone an existing development database C10gDEV as C11gDEV, but on a new server. Let’s use the terms primary and secondary servers. C10gDEV DB resides on the primary server, but C11gDEV DB will reside on the secondary server. The plan is to clone C10gDEV database and then upgrade C11gDEV database to 11g R2. Whatever your requirement may be, the steps described here are still applicable. 

Oracle database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

Step 1 - Assign Oracle SID

Assign the following identifiers to the new database. All identifiers should be different than the database being cloned.

Database Name - C11gDEV
Instance Name - C11gDEV
Service Name - C11gDEV
TNS Connect String – C11gDEV

Step 2 - Allocate Disk Resources

The first step is to allocate sufficient disk space for the new database. For simplicity, assume that all data files and log files are going to the same folder E:\Oradata\C11gDEV. It’s development environment!

Step 3 - Create folder structure

Create the following folders to hold administrative data. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Audit Files - E:\Oradmin\C11gDEV\audit
Background Dump Destination - E:\Oradmin\C11gDEV\bdump
Core Dump Destination - E:\Oradmin\C11gDEV\cdump
User Dump Destination - E:\Oradmin\C11gDEV\udump

Archive Log Destination– E:\Orarch\C10gDEV (if ARCHIVELOG needed)

Please note that the name of the archive log destination is the same on both databases. Change the name later if needed. Otherwise you need to copy the archive logs to new location on the primary server and then run “CATALOG FROM” command to catalog archive logs.

Step 4 - Init.ora Updates

Copy existing C10gDEV’s init.ora file as initC11gDEV.ora, and make the following changes. The change list suggested below should be sufficient for most DBAs, but it’s not a complete list. You may have additional parameters that would require updates.

db_name=C11gDEV
instance_name= C11gDEV
service_names= C11gDEV

Update the following parameters to point to log/trace file locations as described above.

background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest

Update the following parameter to the location where you want to store C11gDEV’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\C11gDEV\control1.ctl’,‘e:\oradata\C11gDEV\control2.ctl’ )

C10GDEV’s datafiles reside on E:\OraData\C10gDEV, but Oracle DBAs have decided to store C11gDEV’s database in E:\OraData\C11gDEV.

Update the following parameters to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV',
                      'd:\oradata\C10gDEV','e:\oradata\C11gDEV' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.

log_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

Step 5 - Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

oradim -new -sid C11gDEV -syspwd verysecretdbpassword -startmode auto -pfile initc11gdev.ora

This command creates a Windows service for C11gDEV database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to C11gDEV database as SYSDBA.

orapwd file=orapwC11gDEV password=verysecretpassword

Please note the password file name.

Step 6 - C11gDEV Instance Startup

Start C11gDEV Oracle database instance with nomount option by:

C:\> set ORACLE_SID=C11gDEV
C:> sqlplus /nolog
SQL> connect / as sysdba
SQL> Startup nomount
SQL> exit

Remember to logout from this SQL*Plus session. Otherwise, your cloning process would hang as new database will be shutdown in normal mode during the cloning process.

Step 7 - Listener Configuration

Add C11gDEV database to listener.ora file. Also create a new TNSNAMES.ORA alias. Use tnsping or SQL*plus to test connectivity by

Sqlplus sys@c10gdev as sysdba

Sqlplus sys@c11gdev as sysdba

Step 8 - Copy Backup to the Secondary Server

Restore on secondary server C10gDEV database’s RMAN backup and required set of archived redo logs to the same location as on the primary server.  RMAN expects the backup sets to be available in the location on the secondary server as primary database server.

If you don’t have the same folder structure available on the secondary server, then you need to perform few additional steps. For example, C10gDEV’s RMAN backup resides on D:\OraBack\C10GDEV, but you need to restore backup to E:\OraBack\C11gDEV on the secondary server. Copy C10gDEV’s RMAN backup to the new location on the primary server, and run CATALOG FROM command to catalog a copy of the backup.

Step 9 - Update RMAN Catalog

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog

RMAN> connect target /

RMAN> crosscheck backup

RMAN> crosscheck archivelog all

RMAN> delete expired backup

RMAN> catalog start with '<RMAN Backup Location>' ;

Step 10 - RMAN Cloning

Start RMAN session as shown below. This example is for RMAN with NOCATALOG.

C:\> set ORACLE_SID=C11gDEV

C:\> rman nocatalog

RMAN> connect target sys@C10gDEV

RMAN> Connect auxiliary /

RMAN> duplicate target database to C11gDEV ;

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to C11gDEV until time '08/03/2010:23:00:00'

Make sure that you specify the same database name above as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

Wow! C11gDEV Database is up and running.

DUPLICATE command has performed the following steps:

• Restored and recovered C10GDEV database
• Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
• New Control files created for C11gDEV database per control_files init.ora parameter
• DB_NAME renamed to C11gDEV
• Mounted C11gDEV database and performed complete media recovery. Applied necessary archive logs to accomplish this.
• GLOBAL_NAME updated
• TEMP files created 
• Opened database with RESETLOGS option

We are greatful, Mr. Export/Import!

We had a production issue yesterday that caused an outage. Export backups came to the rescue and saved the day!

Per organizational security policy, Oracle database passwords of all database accounts, including those used in database links must be changed at regular interval. Database links have been established over secure network between our database and our partner’s database. DB Links are in place for over 10 years, and serving the business purpose of one-directional data/information dissemination. We have DB accounts in our database that our partners use in database links to access our database.

Per organizational security policy, yesterday was the password-change day. DBAs changed the passwords of DB link accounts. When DBAs tried to contact our partner so that they could re-create the database links with the new passwords, there was no one on the other side to re-create the DB link. DBAs tried home and cell, but no avail. A five-minute of planned maintenance window turned into a thirty-minute of production outage, and still no response from the other side. That’s when a decision was made to revert the passwords back to original passwords. But DBAs realized that they didn’t have, and they don’t maintain DB Link accounts passwords. Production outage had reached a 45-minute mark. DBAs tried jogging their memory, referred to old notes, etc., but couldn’t trace the original passwords.

This is when an idea came up! Export backup! This was an Oracle10g database. Nightly export backups were performed. Export dump file stores “CREATE USER username IDENTIFIED BY VALUES ’hash string'” statements. Hash strings represent hashed Oracle passwords. DBAs quickly located last night’s export dump file, grabbed the applicable CREATE USER statements, and changed passwords to their original values with “ALTER USER username IDENTIFIED BY VALUES 'hash string'”. DBAs had to work around password reuse settings in database profiles to restore the passwords, but that wasn’t a problem. DB Links were back in operation! Export saved the day! We are greatful, Mr. Export/Import!

P.S. As of Oracle 11g R2, Export/Import has been desupported in favor of more versatile Data Pump. Export and Import executables are provided with Oracle11g so data from older versions of Oracle can be imported or exported to. Oracle Data Pump import do provide option to retrieve hashed passwords.

Cloning an Oracle Database on the Same Server

Oracle DBAs routinely clone Oracle databases for variety of reasons. This blog explains how to clone an Oracle database on the same server. The requirement for this task is quite simple – clone an existing database. A development Oracle10g instance C10gDEV already exists. We just need to clone this database on the same server as C11gDEV, and then upgrade to 11g later. In this blog, we will talk about the cloning. Upgrade discussion would be part of future blogs.

With RMAN, Oracle DBAs can easily clone the database on the same server with few steps. The time needed to clone the database on the same server is proportional to the time it would take to restore/recover the database plus time to perform the few steps listed below.

In addition to creating or cloning a database for upgrade testing, you may also find few other creative and innovative applications. I have effectively used cloning for the following purposes:

• To quickly create a second database environment on the same server for testing/debugging/tuning.
• To validate and verify RMAN Backup/Recovery procedures by cloning a database and then verifying the data.
• To test Oracle upgrades by cloning the database and then upgrading it. You can also use this approach to test the patches.

Cloning the Database the Same Server technique is quite fast. No need to copy the backups to secondary server. RMAN backups are on disk and available. There are only few steps to perform the cloning.

Caution

• Think twice if you are planning to clone the production database on the same server. Some DBAs supporting mission critical applicaitons may not be able to clone the database on thhe same server. If you are 24 X 7 X 365, then be extra careful.

• Please ensure that you have sufficient CPU, disk and memory capacity on the server to mount a cloned instance and database on the server.

• You may want to perform cloning during maintenance window or off-peak hours to minimize performance impact on the primary database.

• And ensure that your backups are good and recoverable, just in-case!

1.2 Clone the DB!

The requirement for this task is quite simple – clone an existing development database C10gDEV on the same server as C11gDEV. Oracle DBA’s plan is to clone C10gDEV database on the same server and then upgrade C11gDEV database to 11g R2. Existing database version is 10.2.0.4 and operating system is Windows 2008 64-bit.

1.2.1 Assign Oracle SID

Assign the following identifiers to the new database. All identifiers must be different than the identifiers used for the database being cloned.

Database Name - C11gDEV
Instance Name - C11gDEV
Service Name - C11gDEV
TNS Connect String – C11gDEV

1.2.2 Allocate Disk Resources

The first step is to allocate sufficient disk space for the new database. For simplicity, assume that all data files and log files are going to the same folder E:\Oradata\C11gDEV. It’s development environment!

1.2.3 Create folder structure

Create the following folders to hold administrative data. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Audit Files - E:\Oradmin\C11gDEV\audit
Background Dump Destination - E:\Oradmin\C11gDEV\bdump
Core Dump Destination - E:\Oradmin\C11gDEV\cdump
User Dump Destination - E:\Oradmin\C11gDEV\udump
Archive Log Destination– E:\Orarch\C11gDEV (if ARCHIVELOG needed)

1.2.4 Init.ora Updates

Copy existing C10gDEV’s init.ora file as initC11gDEV.ora, and make the following changes. The change list suggested below should be sufficient for most DBAs, but it’s not a complete list. You may have additional parameters that would require updates.

db_name=C11gDEV
instance_name= C11gDEV
service_names= C11gDEV

Update the following parameters to point to log/trace file locations as described above.

background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest

Update the following parameter to the location where you want to store C11gDEV’s control files. In this example, we will use E drive. Make sure that this folder is accessible.

control_files=(‘e:\oradata\C11gDEV\control1.ctl’, ‘e:\oradata\C11gDEV\control2.ctl’ )

C10GDEV’s datafiles reside on E:\OraData\C10gDEV, but Oracle DBAs have decided to store C11gDEV’s database in E:\OraData\C11gDEV.

Update the following parameters to instruct Oracle to translate datafile names during the cloning by specifying db_file_name_convert init.ora parameter.

db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Add additional translation strings to this parameter if you have multiple disk drives hosting the database.

For example,
db_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV',
                                     'd:\oradata\C10gDEV','e:\oradata\C11gDEV' )

Specify log_file_name_convert parameter to instruct Oracle to translate log file names.
log_file_name_convert=('e:\oradata\C10gDEV','e:\oradata\C11gDEV')

Adjust the following memory parameters as per memory availability.

sga_target
sga_max_size
db_cache_size
pga_aggregate_target
java_pool_size
large_pool_size
shared_pool_size
streams_pool_size

Make sure that COMPATIBLE parameter is the same for both the databases. Otherwise, you will get “ORA-01130: database file version incompatible” error.

1.2.5 Service and/or Password File Creation

On Windows platform, create a service for the new database using oradim as shown below:

set ORACLE_SID=C11gDEV

oradim -new -sid C11gDEV -syspwd verysecretdbpassword -startmode auto -pfile initc11gdev.ora

This command creates a Windows service for C11gDEV database. In addition to Windows service, this command also creates a password file. Note that pfile resides in %ORACLE_HOME%\database.

On *nix platforms, you don’t need to create a service, but create a password file by running orapwd. Password file is needed to connect to C11gDEV database as SYSDBA.

orapwd file=orapwC11gDEV password=verysecretpassword

Note the difference in password file names on Windows and Unix. Please be careful with the password file name.

1.2.6 C11gDEV Instance Startup

Start C11gDEV Oracle database instance with nomount option by:

C:\> set ORACLE_SID=C11gDEV
C:\> sqlplus /nolog
SQL> connect / as sysdba
SQL> Startup nomount
SQL> exit

Remember to logout from this SQL*Plus session. Otherwise, your cloning process would hang as Oracle shuts down the new database in normal mode during the cloning process.

1.2.7 Listener Configuration

Add C11gDEV database to listener.ora file. Also create a new TNSNAMES.ORA alias. Use tnsping or SQL*plus to test connectivity.

1.2.8 Update RMAN Catalog

Most likely, you won't need this statp. This step is needed if (1) your objective is to verify database recovery process by restoring the RAMN backups from tape to disk to an alternate location and then clonig the database or (2) RMAN backup location has changed and control file doesn’t have the correct information.

If your objective is to test RMAN backup/recovery procedures, then you want to make sure that RMAN picks up the backups that were restored from the tape. Rename the existing C10gDEV database's RMAN backup location temporarily on disk to a new name, and then perform the following steps. If you don't perform this step, then RMAN would pick up the latest backup on disks, if available.

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog
RMAN> connect target /
RMAN> crosscheck backup
RMAN> crosscheck archivelog all
RMAN> delete expired backup
RMAN> catalog start with 'E:\OraBack\C10gDev\RMANHot' ;

1.2.9 RMAN Cloning

Start RMAN session as shown below. This example is for RMAN with NOCATALOG.

Note that target database is your source database - the database that you want to clone. Auxiliary specifies the new database.

C:\> set ORACLE_SID=C10gDEV

C:\> rman nocatalog
RMAN> connect target /
RMAN> Connect auxiliary sys@C11gDEV
RMAN> duplicate target database to C11gDEV ;

Wow! C11gDEV Database is up and running.

If you want to clone the database till a specific point-in-time, then use the following command.

RMAN> duplicate target database to C11gDEV until time '08/03/2010:23:00:00'

Make sure that you specify the same database name as specified in init.ora file. Otherwise, you would get “RMAN-05520: database name mismatch”

DUPLICATE command has performed the following steps:

• Restored and recovered C10GDEV database
• Datafile and logfile names are converted per db_file_name_convert and log_file_name_convert init.ora parameters
• New Control files created for C11gDEV database per control_files init.ora parameter
• DB_NAME renamed to C11gDEV
• Mounted C11gDEV database and performed complete media recovery. Applied necessary archive logs to accomplish this.
• GLOBAL_NAME updated
• TEMP files created
• Opened database with RESETLOGS option

1.2.10 Log File Excerpts

RMAN> duplicate target database to C11gDEV ;
Starting Duplicate Db at 15-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
   set newname for datafile  1 to ......
   set newname for datafile  2 to ......
   set newname for datafile  3 to ......
   ...........
   ...........
   ...........
   restore
   check readonly
   clone database
   ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
..........
..........
..........
Starting restore at 15-DEC-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to .........
restoring datafile 00002 to .........
restoring datafile 00003 to .........
channel ORA_AUX_DISK_1: reading from backup piece ..........
Finished restore at 15-DEC-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
..........
..........
..........
 CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
.............
.............
.............
contents of Memory Script:
{
   recover
   clone database
   noredo
   ,
    delete archivelog
   ;
}
executing Memory Script
Starting recover at 15-DEC-09
using channel ORA_AUX_DISK_1
Finished recover at 15-DEC-09
contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     629145600 bytes
Fixed Size                     1298288 bytes
Variable Size                171966608 bytes
Database Buffers             452984832 bytes
Redo Buffers                   2895872 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "C11gDEV" RESETLOGS NOARCHIVELOG
...............
...............

renamed temporary file 1 ......
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 15-DEC-09
RMAN>