oraclebasic

Data Pump Import

Let's finish this series on 10G Data Pump by investigating the Data Pump Import utility. In this blog, we'll learn how to execute 10G Data Pump Import and take advantage of some of its more popular features.

We learned in a previous blog that Data Pump Export is used to transfer data from an Oracle database into a flat file. The blog also contained a few quick examples of the benefits that transferring data from an Oracle database to a flat file provides. Now that we have the data in that flat file, we need to learn how to run the Data Pump Import utility to transfer the data back into an Oracle database.

Data Pump Import
Let's begin our discussion with a quick review of a sample Data Pump Import command:

impdp foot/foot directory=data_pump_dir1 dumpfile=expfull.dmp logfile=impfull.log full=y

"impdp" tells the operating system to run the Data Pump Import utility. The parameters that follow are used to control the job's execution. In our example, the account and password are both "foot", the file that will contain the input data is "expfull.dmp", the logfile is"impfull.log" , and a full database import will be performed

We learned previously that you must precreate a directory using a SQL DDL statement on the database being accessed. If you don't pre-create the directory, Oracle provides a default directory called DATA_PUMP_DIR. That means that users are unable to fully qualify the output file and log file as they were able to do in the non Data Pump versions of Export and Import. You specify a fully qualified directory on the server when you create the new database directory object and then you must grant access to that directory. My last blog contains more information on the creation and administration of Oracle directories.

Data Pump provides the following import modes:

Full - Activated by specifying "FULL=Y". Notifies Import to import the entire content of the input file. The amount of data actually imported is dependent upon the data contained in the input file. If the input file contains all of the objects and data from a full database export, all of those objects will be imported. If the input file contains a schema export, all of the objects in that schema will be imported.
Schema Import - Activated by specifying "SCHEMAS=schema, schema, schema….). Imports the entire schema and all dependent objects (tables, indexes, grants, synonyms, PL/SQL programs, views). The problem with the older version of Import was that because it didn't recreate the user (just the user's objects), the administrator was forced to save the user creation and grant statements to recreate the user with the same privileges it had before the drop was performed. A Data Pump schema import will recreate the user and execute all of the associated grants, user password history, etc..
Table Import - Activated by specifying "TABLES=[schemas].tablename, [schemas].tablename,…. Imports a table and all dependent objects.
Tablespace Import - Activated by specifying "TABLESPACES=tablespacename, tablespacename, tablespacename… All of the objects contained in the specified tablespace are imported. If a table is imported, its dependent objects are also imported regardless of the tablespace they were stored in.
Transportable Tablespace Import - Activated by specifying "TRANSPORT TABLESPACES=tablespacename… Here's how this feature works. The administrator first runs Data Pump Export with the Transportable Tablespace feature activated. Data Pump exports only the metadata for the objects contained in the specified tablespaces. The objects in the tablespace set being exported can not have any references to objects contained in tablespaces outside of the tablespace set. In addition there can't be any objects in the tablespace set that are referenced by objects outside of the tablespace set being exported. Having a index in a tablespace that is not contained in the same tablespace set that contains the table it is built upon would be an example of a dependency reference problem. Administrators use the TRANSPORT_FULL_CHECK parameter to perform the dependency verification.

The administrator then uses an operating system command to copy the tablespace datafile(s) to the target database. The transportable tablespace import feature is then used to attach the datafile (and its contents) to the database. Transferring the datafile using operating system commands is much faster than exporting and importing the data using the traditional Data Pump Export and Import operations. Administrators are only limited by hardware and network performance.

It is important to note that you are not forced into using the same import mode as you did for the export. You can use the schema and table option using an export file that was created by a full database export. You can also import one table from a schema level export (and so on…). Lastly, Data Pump Import can only read data that was created by a Data Pump Export.

Let's continue by reviewing a few of the 10G Data Pump Import parameters that you may find interesting. Many of these parameters will be the same as for Data Pump Export.

Data Pump Import Parameters

JOB_NAME=jobname_string - You are able to assign a job name of up to 30 bytes to a Data Pump operation. This allows the job to be more easily identified when querying the DBA_DATAPUMP_JOBS and DBA_DATAPUMP_SESSIONS data dictionary views that provide information on Data Pump operations. If you do not specify a job name, Oracle will create a default job name for you. The form will be SYS_IMPORT_mode_nn, where mode is the type of export (FULL, TABLE, etc.) and "nn" is a incrementing number starting at 01. The job name is used in the ATTACH command (shown below) to attach, and reattach, to running jobs.
ATTACH [=[schema_name.]job_name] - Used to attach a client session to an existing Data Pump operation. An example of the ATTACH command is provided below:

> impdp foot/foot ATTACH=foot.importfulljob
DIRECTORY=directory_object - All of the dumpfile set I/O is generated by Data Pump processes that run within the constructs of the database engine. As a result, the O/S account performing the I/O is Oracle, which usually has a higher level of privileges than most other accounts. Oracle attempts to prevent unwarranted operations from occurring by using Oracle directory objects that require read and write privileges granted by the DBA. Users running Data Pump operations are only able to read and write files in the directories that they have been granted access to.

Before a user is able to run any Data Pump operation, the administrator must pre-create a directory and grant privileges to the user on that directory. This means that users are unable to fully qualify the output file and log file as they were able to do in the non Data Pump versions of Export and Import. If you don't pre-create the directory, Oracle provides a default directory called DATA_PUMP_DIR.
DUMPFILE=[directory_object:]file_name [, ...] - Contains the metadata and/or data output from a Data Pump Export operation that Data Pump Import is using as input. The directory object specification is not required to be specified if a directory is specified in the DIRECTORY parameter. If one is specified in the DIRECTORY parameter and the DUMPFILE= parameter, the DUMPFILE=directory: specification will take precedence.
ESTIMATE={BLOCKS | STATISTICS} - Used to estimate the amount of data a Data Pump Import network operation will generate. This differs from the estimate option that is used with Data Pump Export. Using the ESTIMATE parameter with Data Pump Export will ask Data Pump to estimate how large the output dumpfile will be. I'll provide more information on Data Pump network import operations later in this blog.
Fine Grained Selectivity Parameters (Exclude, Include, Query, Flashback, Content) - Allows the administrator to include or exclude objects. For more information on these parameters, please turn to my previous blog titled "10G Data Pump Part 1".
- HELP = {y | n} - Displays a listing of all the parameters that can be specified for the Data Pump operation. For example:
  
  > impdp help=y
  
  Would provide a listing of parameters for a Data Pump Import operation.
LOGFILE=[directory_object:]file_name - Contains work-in-progress, work completed and errors generated during a Data Pump Operation. For a real time update on a Data Pump operation's activities, you are able to attach to a running Data Pump operation using the ATTACH command and then execute the STATUS command. Like its dumpfile counterpart, The directory object specification is not required to be specified if a directory is specified in the DIRECTORY parameter. If one is specified in the DIRECTORY parameter and the DUMPFILE= parameter, the DUMPFILE=directory: specification will take precedence.
NETWORK_LINK=database_link_name - OK, I must admit that this is one of my favorite new features provided by Data Pump Import. The network transfer option allows administrators to attach to any source database that can be accessed from the network, transfer the data and write it to the target database. No dumpfile required! Administrators use the NETWORK_LINK parameter to specify a pre-created database link that points to the source database.

This parameter must be specified when using the FLASHBACK_SCN, FLASHBACK_TIME, ESTIMATE, or TRANSPORT_TABLESPACES parameters.
PARALLEL=integer - Multiple worker processes are able to perform inter-table and inter-partition work in parallel. This allows administrators to load and unload database tables using parallel processing. Data Pump Import takes advantage of parallelism to build indexes and load package bodies.

The most beneficial aspect of Data Pump's parallel processing feature is that it is dynamic. Administrators are able to attach (connect) to a Data Pump job that is currently executing and dynamically alter the number of parallel processes it uses.

Oracle documentation states that the parallel option may cause file contention if too many parallel processes are used to read the same input dumpfile. As a result, you'll need to be careful when specifying the level of parallelism.
REUSE_DATAFILES={y | n} - Tells Data Pump Import to reinitialize existing tablespace datafiles. Specifying "N" tells Data Pump Import to generate an error message from the failing create tablespace execution. The Import will then continue executing. Specifying "Y" tells Import to issue a warning and reinitialize the tablespace datafile(s) which results in a total loss of existing data in the target tablespace.
STATUS=[integer] - Specifies the number of seconds between each status information display.
TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} - Provides Data Pump Import with instructions to perform when it finds a table in the target database with the same name as a table contained in the input file.
TABLE_EXISTS_ACTION=SKIP - Existing tables are bypassed if they already exist. No action is taken.
TABLE_EXISTS_ACTION=APPEND -Data Pump Import will append rows to the existing table.
TABLE_EXISTS_ACTION=TRUNCATE - Data Pump Import will truncate the existing table and load rows from the source.
TABLE_EXISTS_ACTION=REPLACE - Existing tables will be dropped, recreated and then loaded from the source.
- TRANSFORM = transform_name:value[:object_type] - Allows the administrator to alter object creation DDL for specified objects . For more information on these parameters, please turn to my previous blog titled "10G Data Pump Part 1".

Commands Available in Interactive Mode
We learned previously that administrators are able to attach and detach from actively running Data Pump operations. Administrators use the ATTACH and EXIT_CLIENT commands to connect and disconnect from an active Data Pump job. This output file provides a demonstration of detaching and attaching from an active Data Pump operation. You'll notice that I dynamically change the parallel setting after I reattach.

Let's take a look at some of the other parameters for Data Pump Import that you can dynamically change during its execution:

CONTINUE_CLIENT - Switches from interactive mode to status (logging) mode. An example of this command follows:

impdp> CONTINUE_CLIENT
EXIT_CLIENT - Detaches the client from an active Data Pump operation. The job continues to run. The administrator is then able to use the ATTACH command to reattach to the job at any time. The attach can be performed if the job is executing or in a stopped state. An error message will be returned if an attempt is made to attach to a completed Data Pump Operation.
HELP - Provides help on interactive mode Data Pump commands.
KILL_JOB - Kills the active Data Pump operation and detaches all client sessions. This command differs from the STOP_JOB command which is discussed below. The KILL_JOB will remove DUMPFILEs that were created which prevents the user from taking advantage of Data Pump's restart capabilities. The log files will remain.
PARALLEL=integer - Change the number of parallel processes for a Data Pump operation.
START_JOB / STOP _JOB - Restarts and stops the Data Pump operation the client is currently attached to. Data Pump jobs update a master table with all pertinent information pertaining to the job being executed. The master table knows about the objects being exported or imported and their location in the dump file set.

As a result, a Data Pump job can be restarted in the event of a planned or unplanned stoppage. The Data Pump job accesses the master table to determine where to start and picks up right where it left off. Administrators are able to use the STOP_JOB AND START_JOB commands on the client to temporarily stop jobs and restart them when desired.

Oracle provides the SKIP_CURRENT option of the START_JOB parameter to allow administrators to restart a Data Pump Import operation that failed to restart because of a failing DDL statement. SKIP_CURRENT tells Data Pump Import to skip the object having the problem and restart processing on the next object.
STATUS=[integer] - Specifies the number of seconds between each status information display.

WRAPUP
I think after reading these last four blogs, we should all have a pretty good understanding of 10G Data Pump. We can finally retire Data Pump's ancestors Export and Import. As we now know, they just can't compete with the features and functionality provided by Data Pump Export and Import.

Thanks for reading.

Chris Foot
Oracle Ace

Data Pump Import script

** Listing the different directories available to me. I'll be running the jobs

** with an account that has DBA authorities so I don't have to worry about permissions.

** You must either specify a directory, or use the default one (DATA_PUMP_DIR) that

** is created by Oracle during installation. I'll use the default one. That way I

** won't have to hardcode a directory on my Data Pump command line.

SQL> r

1* select directory_name, directory_path from dba_directories

DIRECTORY_NAME DIRECTORY_PATH

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

SUBDIR /app/oracle/product/10.2/db/demo/schema/order_entry//2002/Sep

XMLDIR /app/oracle/product/10.2/db/demo/schema/order_entry/

MEDIA_DIR /app/oracle/product/10.2/db/demo/schema/product_media/

LOG_FILE_DIR /app/oracle/product/10.2/db/demo/schema/log/

WORK_DIR /ade/aime_10.2_lnx_push/oracle/work

DATA_FILE_DIR /app/oracle/product/10.2/db/demo/schema/sales_history/

DATA_PUMP_DIR /app/oracle/product/10.2/db/admin/ctitst/dpdump/

ADMIN_DIR /ade/aime_10.2_lnx_push/oracle/md/admin

** Running a Data Pump Export. Account is foot, password iS foot,

** output file is expfull.dmp, logfile is expfull.log and it is a full

** database export. Since I'm using the default Data Pump Directory,

** I won't have to specify it on the command line.

$ expdp foot/foot dumpfile=expfull.dmp logfile=expfull.log full=y

** Data Pump responds back to the client with the traditional utility

** processing messages.

Export: Release 10.2.0.1.0 - Production on Thursday, 18 May, 2006 6:46:27

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "FOOT"."SYS_EXPORT_FULL_01": foot/******** dumpfile=expfull.dmp logfil

e=expfull.log full=y

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHA

SEORDER" will be skipped.

Total estimation using BLOCKS method: 59.37 MB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

<CTL C>

** OK, here is where I hit CONTROL-C to break out of the client session.

** The Data Pump Export continues to run. It displays the prompt below:

Export>

** I typed in EXIT to disconnect from the client session and return to the

** Operating System.

Export> exit

** I created another session to the database and started another Data Pump Export.

** I created a third session and activated SQL*PLUS to select from DBA_DATAPUMP_JOBS.

** Notice that I still have a client attached to my second Export. The first one

** that I broke out of is still running but shows no attached (connected) sessions.

1* select * from dba_datapump_jobs

OWNER JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

FOOT SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 0 2

FOOT SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA EXECUTING 1 1 3

** I am attaching back to the first Export using the default job name that Oracle supplied. I can

** also choose to name the job when I first submit it.

$ expdp foot/foot attach=SYS_EXPORT_FULL_01

** Data Pump responds by giving me a status

Export: Release 10.2.0.1.0 - Production on Thursday, 18 May, 2006 6:48:13

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Job: SYS_EXPORT_FULL_01

Owner: FOOT

Operation: EXPORT

Creator Privs: FALSE

GUID: 140DBFFB575BF0CFE0400A0A2D0A612A

Start Time: Thursday, 18 May, 2006 6:46:35

Mode: FULL

Instance: ctitst

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND foot/******** dumpfile=expfull.dmp logfile=expfull.log full=y

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /app/oracle/product/10.2/db/admin/ctitst/dpdump/expfull.dmp

bytes written: 4,096

Worker 1 Status:

State: EXECUTING

Object Schema: BI

Object Name: SALES

Object Type: DATABASE_EXPORT/SCHEMA/SYNONYM

Completed Objects: 22

Total Objects: 22

Worker Parallelism: 1

** I can also manually enter a STATUS command at any time to see the status of the job.

** Note that I only have one worker executing.

Export>

Export> status

Job: SYS_EXPORT_FULL_01

Operation: EXPORT

Mode: FULL

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /app/oracle/product/10.2/db/admin/ctitst/dpdump/expfull.dmp

bytes written: 4,096

Worker 1 Status:

State: EXECUTING

Object Schema: SYSMAN

Object Name: MGMT_DB_FEATUREUSAGE

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Completed Objects: 471

Worker Parallelism: 1

Export>

** I can use the PARALLEL command line parameter to dynamically change the job's number of

** parallel processes.

Export> parallel=2

** Executing another STATUS command to confirm if the job is indeed using parallel workers.

** We can see two workers are now running and the Current Parallelism indicator is now 2.

Export> status

Job: SYS_EXPORT_FULL_01

Operation: EXPORT

Mode: FULL

State: EXECUTING

Bytes Processed: 48,674,768

Percent Done: 72

Current Parallelism: 2

Job Error Count: 0

Dump File: /app/oracle/product/10.2/db/admin/ctitst/dpdump/expfull.dmp

bytes written: 54,796,288

Worker 1 Status:

State: EXECUTING

Object Schema: SYSMAN

Object Name: MGMT_HC_HARDWARE_MASTER

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 55

Total Objects: 626

Worker 2 Status:

State: EXECUTING

Object Schema: SH

Object Name: COSTS

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Completed Objects: 24

Total Objects: 626

** Going back to my SQL*PLUS session to see if we have additional workers.

SQL> select * from dba_datapump_sessions;

OWNER JOB_NAME SADDR SESSION_TYPE

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

FOOT SYS_EXPORT_FULL_01 29EFE64C DBMS_DATAPUMP

FOOT SYS_EXPORT_FULL_01 29EEBB0C MASTER

FOOT SYS_EXPORT_FULL_01 29EECDC0 WORKER

** Attempting to attach again. Data Pump can't find the job because it is complete.

$ expdp foot/foot attach=SYS_EXPORT_FULL_01

Export: Release 10.2.0.1.0 - Production on Thursday, 18 May, 2006 6:54:56

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.KUPV$FT", line 430

ORA-31638: cannot attach to job SYS_EXPORT_FULL_01 for user FOOT

ORA-31632: master table "FOOT.SYS_EXPORT_FULL_01" not found, invalid, or inaccessible

ORA-00942: table or view does not exist

Oracle 10G Hidden Features Part II

VERSION 2

Created on: Mar 25, 2010 12:51 PM by saudamini_raman - Last Modified: Mar 30, 2010 7:14 AM by saudamini_raman

With all of new features contained in this release, Oracle10G promises to be the most exciting Oracle release to date. This is part two of a series highlighting some of the hidden features that Oracle customers can look forward to when using the "latest and greatest" version of Oracle's flagship database product, Oracle10G.

The Hidden Secrets of Oracle10G Blog Part II

Consider this to be the exciting sequel to The Hidden Secrets of Oracle10G Blog Part I. This series of blogs are not intended to be an inclusive list of Oracle10G hidden features. I'm hoping to provide readers with information on some of the 10G benefits not covered by other authors. If you have a hidden feature you would like to highlight, please feel free to respond to this blog. That's what blogging is all about!

Shrinking Tables

Getting rid of unused space in a table improves the performance of full table scans. Removing unused space in index structures provides quicker index access due to a more compact tree. The additional benefit is a more efficient utilization of space because the space freed is available for other objects. In releases prior to Oracle10G, getting rid of free space above and below a table's high-water mark was usually done by the tried-and-true, Export, Drop Table, Import commands. Administrators could also use the ALTER INDEX REBUILD statement to reorganize index segments to create a more compact index tree.

Oracle10G objects that reside in Automatic Segment Space Managed (ASSM) tablespaces can be shrunk using the "ALTER………SHRINK" statement. The shrink statement is not limited to just heap tables and indexes, Oracle also allows index-organized tables, partitions, subpartitions, materialized views and materialized view logs to be shrunk using ALTER commands. In addition, the CASCADE option can be used to propagate the shrink operation to all dependent objects except materialized views, LOB indexes, IOT mapping tables and overflow segments.

It is interesting to note that because the shrink operation may change the ROWIDS in heap-organized tables, row movement must first be enabled by executing the "ALTER TABLE tablename ENABLE ROW MOVEMENT" statement. The shrink operation can be performed while the table is on-line and active and does not require any extra database data file space. The shrink operation itself is performed by the database internally executing INSERT and DELETE statements. Since the data itself is not changed (just rearranged), DML triggers are not fired during shrink operations.

Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space. As the amount of data grows, the segment's high water mark grows accordingly. But as the amount of data shrinks (i.e. row deletes), the high water mark is not altered to reflect the segment's new characteristics. The high water mark not being adjusted as the data shrinks has the tendency to create a somewhat confusing performance problem.

During a full table scan, Oracle scans all blocks up to the table's high water mark. This happens regardless of whether those blocks contain data or not. Business needs often require that all rows of a table be scanned. DBAs often attempt to increase the performance of the aforementioned table scans by trying to store as little data as possible in the scanned table. If the table is purged by SQL DELETE statements, the high water mark will not be moved and the table scans will continue to perform poorly. Please note that future INSERT statements can reuse the space. Until that happens, you may be scanning a lot of blocks and not retrieving any data. 10G's new shrink command will definitely help in this situation.

If all of the rows are being deleted, the administrator should use the TRUNCATE statement to remove the unwanted rows. TRUNCATE adjusts the high water mark to the first block in the segment. If a partial purge is being performed, the administrator should reorganize the table with EXPORT/IMPORT or the ALTER TABLE MOVE command in Oracle9i or the new segment shrink command in Oracle10G.

Segment Advisor

Administrators are able to use Oracle10G's Segment Advisor to identify candidates for shrink operations. The advisor estimates the amount of unused space that will be released when the shrink operation is run on the particular object. A wizard is available that allows users to evaluate all objects in the database, all objects in a specific tablespace or all objects owned by a particular schema.

Although we have tested this in our labs, we have yet to test the segment advisor in an active 10G environment. The DBA running the 10G project here likes to "aggressively test and implement" new releases here. Rest assured that in future blogs, I'll be providing you with a wealth of information on the segment shrink statement and the segment advisor.

Tablespace Rename
Tablespaces can be renamed in Oracle10G by executing the "ALTER TABLESPACE old_name TO new_ name" SQL statement. Oracle10G allows permanent, temporary and undo tablespaces to be renamed. Oracle will automatically update all tablespace name references in the data dictionary, control files and on-line data file headers. If the tablespace being renamed is an undo tablespace, Oracle will perform the additional step of updating the UNDO_TABLESPACE parameter in the SPFILE. If an SPFILE is not used, a message is written to the database's alert log notifying administrators that the parameter file must be manually changed to reflect the undo tablespace's new name.

The way my DBAs type, I'm happy to see ANY new rename statement become available in Oracle. I'll put it to you this way, if Mavis Beacon ever stopped by and saw our DBAs in action; she'd probably have to use the old paper bag trick to stop from hyperventilating. I won't be cringing as much now when I hear "what do you mean I spelled the name wrong?" coming from my team.

Oracle File Copies
Oracle10G's DBMS_FILE_TRANSFER PL/SQL package provides administrators with a mechanism to copy binary files between Oracle databases without using OS commands or FTP. The transfer package can be executed locally to transfer files to another database server or can be executed remotely to transfer files between two remote databases.

Currently, the only files that can be copied using this mechanism are Data Pump dump sets and tablespace data files. In addition, the file size must be a multiple of 512 bytes and less than 2 terabytes. Using the file transfer package in conjunction with Oracle's transportable tablespace feature allows administrators to totally automate tablespace data transfers from one database to another. The process to unplug tablespace data files from the source database, copy the files to the destination server and plug the tablespace data files into the target database can now be executed on a recurring basis by batch jobs initiated by DBMS_JOBS, OEM, KRON, AT and third-party schedulers. The transferred files created on the target platforms are owned by the Oracle account and can be accessed by all database processes. For long copy operations, progress is displayed in the V$SESSION_LONGOPS view.

Redo Logfile Size Advisor
Describing the process of determining the size of a database's redo logfile as "somewhat error-prone" is like stating that the Titanic sprung a small leak. Administrators must balance the performance implications of redo logfiles that are too small with the recovery implications of having redo logfiles that are too large. Oracle10G comes to the rescue with another new advisor, the Redo Logfile Size Advisor. The advisor suggests the smallest on-line redo logfile based on the current FAST_START_MTTR_TARGET parameter and workload statistics. Like database managed undo segments, Oracle must have thought we were doing such a "bang up" job sizing redo logfiles that they felt we needed help. For many of us, they were right!

I can't under estimate the importance that redo logfile sizing has on database performance. When I was a high-priced Oracle consultant on a tuning gig, one of the first things I looked at when I performed my database environment performance review was to look at log switch frequency. If I saw "checkpoint not complete" messages in the alert log, I'd start to salivate knowing that fixing this problem was going to be an easy way to tune the client's environment.

A common rule of thumb is to adjust the redo log's size so that Oracle performs a log switch every 15 to 30 minutes. Log switches that occur more frequently may have a negative impact on performance. Log switches that occur several times a minute have a definite impact on database performance. Checking messages in the alert log is one way to determine how fast Oracle is filling and switching logs. If the following messages are found, you can be sure that performance is being affected:

Thread 1 advanced to log sequence 248
Current log# 2 seq# 248 mem# 0: /orant/oradata/logs/redolog2a.log
Thread 1 cannot allocate new log, sequence 249
Checkpoint not complete

The "checkpoint not complete" messages are generated because the logs are switching so fast that the checkpoint associated with the log switch isn't complete. During that time, Oracle's LGWR process has filled up the other redo log groups and is now waiting for the first checkpoint to successfully execute. Oracle will stop processing until the checkpoint completes successfully.

One easy way to prevent improper redo logfile sizing from affecting database performance in Oracle10G is to use the handy new redo logfile size advisor.

Flushing the Buffer Cache
The buffer cache can magically increase the performance of SQL benchmarking tests. The first run will incur the I/O to load the data buffers and subsequent runs may be faster because the data is now cached. The "ALTER SYSTEM FLUSH SHARED POOL" empties the database buffer cache and allows users to establish and maintain a consistent SQL benchmark environment. Administrators are able to flush the buffer cache between statement executions to determine if their tuning changes have made a positive impact on the execution times of the SQL statement being tuned.

Even though I am an ex-Oracle instructor, I still have a strong desire to train others. If it wasn't for my wife forgetting who I was, I would still be teaching. One of my favorite teases for really new DBAs was to run a query and time it, tap the top of the monitor and run it again. Becuase the first query "primed" or pre-loaded the buffers, the run-time would magically be reduced! It was an excellent way to start our discussion on the benefits of properly sizing the database buffer cache.

Active Session History
Oracle10G contains a new internal utility, called Active Session History, to provide administrators with access to current performance information.

Active Session History samples data from the V$SESSION dynamic performance table every second and stores the information in V$ACTIVE_SESSON_HISTORY. The information contains the events that current sessions are waiting for. The information pertains to active sessions only; information from inactive sessions is not recorded. The view contains one row per active session for each one-second sample. Administrators are able to access V$ACTIVE_SESSON_HISTORY as they would any other V$ dynamic performance table.

It is important to note that like V$SQLAREA, V$ACTIVE_SESSION_HISTORY is a rolling buffer in memory. Oracle's internal documentation states that current workload analysis most often requires detailed performance statistics on activity within the last five to ten minute time-period. As new information is added, earlier information contained in the view will be removed. Active Session History consumes 2 megabytes of memory per CPU and is fixed for the lifetime of the instance.

I've been tuning for a long time, having started with Oracle Version 6. I've also been teaching Oracle for a long time. Here is some helpful advice, learn as much as you can about Active Session History. This new feature WILL REDUCE the amount of time you spend tuning applications that use Oracle.

Conclusion
As stated previously, the intent of these blogs were to highlight a few of the interesting, but often overlooked, new features in Oracle10G. Thanks for reading and good luck with Oracle10G. Please feel free to respond back with any comments, corrections or questions!

oraclebasic

Search This Blog

Tuesday, November 23, 2010

No comments:

Post a Comment