About Netbackup

NetBackup integrates the database backup and recovery capabilities of the Oracle Recovery Manager (RMAN) with the backup and recovery management capabilities of NetBackup. NetBackup for Oracle also lets you export and import Oracle data in XML format for long-term archival and retrieval.

Wait Event Enhancements in Oracle 10g

Oracle Database 10g Enhanced wait model [ID 245055.1]
		Modified 26-JUN-2007     Type BULLETIN     Status PUBLISHED

PURPOSE

This bulletin outlines Enhanced wait model introduced in Oracle Database 10G.

SCOPE & APPLICATION

The Oracle Database 10g has many improvements for the wait reporting mechanism. These include:

1. Blocking Session
2. Classification of Events
3. Break out important latch events
4. Histogram
5. Session and System Level Event Class Stats
6. Combine V$SESSION_WAIT into V$SESSION
7. Added V$SESSION_WAIT_HISTORY
8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

1. Blocking Session

Two new columns blocking_session and blocking_session_status have been added to V$SESSION view.

Column "blocking_session" would contain the session id of the resource holder for which a session is waiting for. Else it would contain Null.

Column "blocking_session_status" would contain the status of the value of the blocking_session column.

The Value of the columns could be:

• VALID:-                         A valid Session ID is present in the Blocking Session column
• NO HOLDER:-              which implies that there are no holders of this resource.
• UNKNOWN:-               which implies that we could not figure out the holder.
• UNIMPLEMENTED:-   which implies that the callback for the event has not been implemented
• GLOBAL:-                     which implies that the holder is a session on another instance.

Following query would be useful to find the blocking session.

SELECT sid, blocking_session, username, blocking_session_status

FROM v$session

ORDER BY blocking_session;

2. Classification of Events 

All wait events have been classified into categories like contention wait/OS service wait/DB Service ,Wait/idle wait, etc.. This will enable the user to immediately find out whether the system is performing poorly due to excessive contention or a background not performing well or whether the Operating System does not have enough resources.

A category has many wait events assigned to it, so the below example shown displays the total waits reported for each class. The wait event class gives an overall view of a particular area. For example, the I/O wait category contains all wait events associated with disk I/O. As a rule, the wait event categories with the highest wait times and counts become the focus of tuning effort.

Example :

col wait_class format a30

SELECT e.wait_class#, e.wait_class,sum(s.total_waits), sum(s.time_waited)

FROM  v$event_name e, v$system_event s

WHERE  e.name = s.event

GROUP BY e.wait_class#,e.wait_class;

Classify the wait events into:

• Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.
• Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).
• Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.
• Administrative: These are waits imposed by a privileged users by some action.
• Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.
• Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.
• Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.
• User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON
• System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON
• Scheduler: These are waits due to the resource manager
• Cluster: waits which will occur only in RAC mode.
• Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.

3. Break out important latch events

Before Oracle Database 10g, it is not possible to see the latch waits on a session basis. When a session is waiting it waits on the latch free event, which does not tell much. One needs to see p1, which gives address, and find out which latch is it. Also, One does not know which are the sessions and which are getting the latch and resulting in contention.

Thus, the high contention in the database are breaking out separate wait events for certain latches which have known to be points of past. Some latches events will also have different wait classes. All the event names will have prefix of “latch: “ so that users can get all latch events by writing like ‘latch: %’.

Example:

SELECT event,p1,p2,p3 

FROM v$session_wait

WHERE event like 'latch%';

4. Histogram

The following views have been introduced from Oracle Database 10G which would be useful to generate histogram for system events and I/Os at file level.

• V$EVENT_HISTOGRAM
• V$FILE_HISTOGRAM
• V$TEMP_HISTOGRAM

To collect histogram data in these views database parameter timed_statistics should be set to TRUE.

4.a V$EVENT_HISTOGRAM

The v$system_event view displays the number of waits, the maximum wait time, and the total time waited per wait event. This view that show wait event information do not offer information on the time of each wait event occurrence. A single very long wait can skew the information offered by this view, these waits might not be indicative of the system as a whole.

The v$event_histogram view remedies this. Using this view, you create a histogram showing the frequency of wait events for a range of durations. To provide you with more in depth data the v$event_histogram view shows the number of waits for a wait event over a range of time values. You use the v$event_histogram view to determine if the bottleneck is a regular or a unique problem.

This view has the following columns:

• event# :- The number assigned to the wait event. This corresponds to the event# column in the v$event_name table.
• Event:- The name of the wait event. This corresponds to the name column in the v$event_name table.
• wait_time_milli:- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• wait_count:- The number of waits of duration belonging to this bucket of the histogram

4.b V$FILE_HISTOGRAM

The v$filestat view displays statistics per data file. This information can be skewed due to a couple of very large I/O waits, thus these waits might not be indicative of the system as a whole.

V$FILE_HISTOGRAM displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms. The histogram can be used to determine if the bottleneck is a regular or a unique problem.The frequency of each wait event versus the length of the wait events would be seen using the information in the v$file_histogram view.

The following columns make up the v$file_histogram view:

• FILE#:- File number
• SINGLEBLKRDTIM_MILLI :-The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram

4.c V$TEMP_HISTOGRAM

V$TEMP_HISTOGRAM for temporary files. V$TEMP_HISTOGRAM displays a histogram of all single block reads on a per-tempfile basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

• FILE# :- File number
• SINGLEBLKRDTIM_MILLI :- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
• SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram.

5. Session and System Level Event Class Stats

The following views have been introduced from Oracle Database 10G, which display total wait wide instance and session.

•   v$system_wait_class
•   v$session_wait_class

5.a v$system_wait_class

The v$system_wait_class view displays the instance wide totals of the time waited and the number of times wait events of this class have occurred. Both of these totals are running totals started when the instance started.

The view consists of the following columns:

• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered
• time_waited :- The amount of time spent in this wait by all sessions in the instance
• total_waits :- The number of times waits of this class occurred

From the output of the below query you can determine which classes of wait event you should investigate further.

SELECT wait_class#, wait_class, time_waited, total_waits

FROM v$system_wait_class

ORDR BY time_waited;

5.b v$session_wait_class

The v$session_wait_class view shows the time spent in various classes of wait event operations on a per session basis. The view includes the following columns:

• sid :- The session id (same as in v$sesstat)
• serial# :- The serial number
• wait_class# :- The wait class number
• wait_class :- The name given to the wait class when registered.
• time_waited :- The amount of time spent in this wait class by this session
• total_waits :- The number of times waits of this class occurred for this session

After determining that a wait event class is affecting performance (from the v$system_wait_class view) use v$session to determine the sessions that are experiencing the specific wait event class.

Use the below SQL statement to determine the sessions that have wait events associated with this class.

SELECT  sid,serial#,time_waited,total_waits

FROM v$session_wait_class

WHERE  wait_class# =

ORDER BY time_waited;

Note: Do not expect the wait_time and wait_count columns to add up to the total in v$system_wait_class since it is likely that some sessions would have already ended and therefore would not appear in the v$session_wait_class view.

6. Combine V$SESSION_WAIT into V$SESSION

In the previous releases to determine the sessions experiencing waits, we need to join the v$session_wait view with the v$session view.

In the Oracle Database 10g all wait event columns from v$session_wait have been added to v$session thus increasing performance by eliminating the overhead of joins.

7. Added V$SESSION_WAIT_HISTORY

In the previous releases, we cannot see the last few waits of a session. From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.

The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.

We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.

SELECT sid,seq#,event

FROM v$session_wait_history

WHERE sid = ;

8. NEW "WAIT_CLASS" columns in V$EVENT_NAME

The following columns have been added to the v$event_name view:

• wait_class# :- The wait class number
• wait_class :- The name of the class of wait events

Following query can check waits caused by I/O:

SELECT name, wait_class#, wait_class

FROM v$event_name

WHERE wait_class# in (10,11);

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Back to top

Rate this document 

Article Rating Rate this document Excellent Good Poor Did this document help you? Yes No Just browsing How easy was it to find this document? Very easy Somewhat easy Not easy		Comments Provide feedback for this article. Please use 'Contact Us' for other feedback. Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel

DB Segments Used (Type wise)

Create Or Replace Force View Sys.Av_db_used_space_seg_type ("Total Used",
                                                            "Data part",
                                                            "Index part",
                                                            "LOB part",
                                                            "RBS part",
                                                            "TEMP part"
                                                           )
As
   Select Sum (Bytes) / 1024 / 1024 "Total Used",
          Sum (Decode (Substr (Segment_type, 1, 5),
                       'TABLE', Bytes / 1024 / 1024,
                       0
                      )
              ) "Data part",
          Sum (Decode (Substr (Segment_type, 1, 5),
                       'INDEX', Bytes / 1024 / 1024,
                       0
                      )
              ) "Index part",
          Sum (Decode (Substr (Segment_type, 1, 3),
                       'LOB', Bytes / 1024 / 1024,
                       0
                      )
              ) "LOB part",
          Sum (Decode (Segment_type, 'ROLLBACK', Bytes / 1024 / 1024, 0)
              ) "RBS part",
          Sum (Decode (Segment_type, 'TEMPORARY', Bytes / 1024 / 1024, 0)
              ) "TEMP part"
     From Sys.Dba_segments;

DB History

Create Or Replace Force View Sys.Av_db_update_history (Action_time,
                                                        Action,
                                                        Namespace,
                                                        Version,
                                                        Id,
                                                        Comments
                                                       )
As
   Select "ACTION_TIME", "ACTION", "NAMESPACE", "VERSION", "ID", "COMMENTS"
     From Registry$history;

How much DB undo generated

Create Or Replace Force View Sys.Av_db_undo_generated (Rundate,
                                                       Day,
                                                       Logswitch,
                                                       "REDO PER DAY (MB)"
                                                      )
As
   Select   Trunc (Completion_time) Rundate,
            To_char (Trunc (Completion_time), 'DAY') Day, Count (*) Logswitch,
            Round ((Sum (Blocks * Block_size) / 1024 / 1024)
                  ) "REDO PER DAY (MB)"
       From V$archived_log
   Group By Trunc (Completion_time)
   Order By 1 Desc;

Session blocking quiesce

Create Or Replace Force View Sys.Av_db_sess_blking_quiesc (Sid,
                                                           Sess_user,
                                                           Osuser,
                                                           Type,
                                                           Program,
                                                           Killstmt
                                                          )
As
   Select Bl.Sid, User Sess_user, Osuser, Type, Program,
             'Alter SYSTEM DISCONNECT Session '
          || ''''
          || Se.Sid
          || ','
          || Se.Serial#
          || ''' '
          || 'IMMEDIATE;' Killstmt
     From V$blocking_quiesce Bl, V$session Se
    Where Bl.Sid = Se.Sid;

DB overall efficiency

Create Or Replace Force View Sys.Av_db_overall_eff (Metric_name,
                                                    Mininum,
                                                    Maximum,
                                                    Average
                                                   )
As
   Select   Case Metric_name
               When 'SQL Service Response Time'
                  Then 'SQL Service Response Time (secs)'
               When 'Response Time Per Txn'
                  Then 'Response Time Per Txn (secs)'
               Else Metric_name
            End Metric_name,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Minval / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Minval / 100), 2)
               Else Minval
            End Mininum,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Maxval / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Maxval / 100), 2)
               Else Maxval
            End Maximum,
            Case Metric_name
               When 'SQL Service Response Time'
                  Then Round ((Average / 100), 2)
               When 'Response Time Per Txn'
                  Then Round ((Average / 100), 2)
               Else Average
            End Average
       From Sys.V_$sysmetric_summary
      Where Metric_name In
               ('CPU Usage Per Sec', 'CPU Usage Per Txn',
                'Database CPU Time Ratio', 'Database Wait Time Ratio',
                'Executions Per Sec', 'Executions Per Txn',
                'Response Time Per Txn', 'SQL Service Response Time',
                'User Transaction Per Sec')
   Order By 1;

Hard activities in DB

Create Or Replace Force View Sys.Av_db_hard_activities (Db_stat_name,
                                                        Time_secs,
                                                        Pct_time
                                                       )
As
   Select   Case Db_stat_name
               When 'parse time elapsed'
                  Then 'soft parse time'
               Else Db_stat_name
            End Db_stat_name,
            Case Db_stat_name
               When 'sql execute elapsed time'
                  Then Time_secs - Plsql_time
               When 'parse time elapsed'
                  Then Time_secs - Hard_parse_time
               Else Time_secs
            End Time_secs,
            Case Db_stat_name
               When 'sql execute elapsed time'
                  Then Round (100 * (Time_secs - Plsql_time) / Db_time,
                              2
                             )
               When 'parse time elapsed'
                  Then Round (100 * (Time_secs - Hard_parse_time) / Db_time,
                              2)
               Else Round (100 * Time_secs / Db_time, 2)
            End Pct_time
       From (Select Stat_name Db_stat_name,
                    Round ((Value / 1000000), 3) Time_secs
               From Sys.V_$sys_time_model
              Where Stat_name Not In
                       ('DB time', 'background elapsed time',
                        'background cpu time', 'DB CPU')),
            (Select Round ((Value / 1000000), 3) Db_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'DB time'),
            (Select Round ((Value / 1000000), 3) Plsql_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'PL/SQL execution elapsed time'),
            (Select Round ((Value / 1000000), 3) Hard_parse_time
               From Sys.V_$sys_time_model
              Where Stat_name = 'hard parse elapsed time')
   Order By 2 Desc;

DB Global waits

Create Or Replace Force View Sys.Av_db_global_waits (Wait_class,
                                                     Total_waits,
                                                     Pct_waits,
                                                     Time_waited_secs,
                                                     Pct_time
                                                    )

DB General Info

Create Or Replace Force View Sys.Av_db_gen_info (Db_info)
As
   Select '01- ' || Banner Db_info
     From Sys.V_$version

Data Files free space

Create Or Replace Force View Sys.Av_db_df_free_space (Tablespace_name,
                                                      File_name,
                                                      Allocated_mb,
                                                      Used_mb,
                                                      Free_space_mb
                                                     )

Current session wait activity in DB

Create Or Replace Force View Sys.Av_db_cur_sess_wait_activity (Sid,
                                                               Username,
                                                               Wait_class,
                                                               Total_waits,
                                                               Time_waited_secs,
                                                               Killstmt
                                                              )
As
   Select   A.Sid, B.Username, A.Wait_class, A.Total_waits,
            Round ((A.Time_waited / 100), 2) Time_waited_secs,
               'Alter SYSTEM Kill Session '
            || ''''
            || B.Sid
            || ','
            || B.Serial#
            || ''' '
            || 'IMMEDIATE;' Killstmt
       From Sys.V_$session_wait_class A, Sys.V_$session B
      Where B.Sid = A.Sid
        And B.Username Is Not Null
        And A.Wait_class != 'Idle'
   Order By 5 Desc;

Active Process in DB

Create Or Replace Force View Sys.Av_db_active_process (Username,
                                                       Osuser,
                                                       Disk_reads,
                                                       Buffer_gets,
                                                       Lockwait,
                                                       Pid,
                                                       Event,
                                                       Sql
                                                      )

Blocking Waiting Sessions

Create Or Replace Force View Sys.Av_blocking_waiting_sess (Waiting_session,
                                                           Holding_session,
                                                           Lock_or_pin,
                                                           Address,
                                                           Mode_held,
                                                           Mode_requested
                                                          )

Locked Object

Create Or Replace Force View Sys.Av_locked_obj (Sid,
                                                Oracle_username,
                                                Os_user_name,
                                                Locked_mode,
                                                Object_name,

Object Cache Locks

Create Or Replace Force View Sys.Av_lock_object_cache (Owner,
                                                       Name,
                                                       Db_link,
                                                       Namespace,

Library Cache Locks

Create Or Replace Force View Sys.Av_lock_lib_cache (Sid,
                                                    Lockmode,
                                                    Lockrequest,

DDL Locks on Objects

Create Or Replace Force View Sys.Av_lock_ddl_on_obj (Sid,
                                                     Serial#,
                                                     Owner,
                                                     Obj_name,

ASM Diskgroup Info

Create Or Replace Force View Sys.Av_asm_diskgroup (Group_number,
                                                   Name,
                                                   Sector_size,
                                                   Block_size,
                                                   Allocation_unit_size,
                                                   State,
                                                   Type,
                                                   Total_mb,
                                                   Free_mb,
                                                   Required_mirror_free_mb,
                                                   Usable_file_mb,
                                                   Offline_disks,
                                                   Unbalanced,
                                                   Compatibility,
                                                   Database_compatibility
                                                  )
As
   Select "GROUP_NUMBER", "NAME", "SECTOR_SIZE", "BLOCK_SIZE",
          "ALLOCATION_UNIT_SIZE", "STATE", "TYPE", "TOTAL_MB", "FREE_MB",
          "REQUIRED_MIRROR_FREE_MB", "USABLE_FILE_MB", "OFFLINE_DISKS",
          "UNBALANCED", "COMPATIBILITY", "DATABASE_COMPATIBILITY"
     From V$asm_diskgroup;

ASM Disk Info

Create Or Replace Force View Sys.Av_asm_disk (Name,
                                              Path,
                                              Header_status,
                                              Free_mb,

How can I tell if a procedure/package is running?

Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package.

Listing privileges recursively for Oracle users

This is a script that shows the hierarchical relationship between system privileges, roles and users.

FRM-92100 when running a long running report

Users were experiencing the FRM-92100 with IllegalArgumentException while running a long running report, after investigation it was found that Request Timeout (seconds) value was 600 only on OHS.  So changing to 1500 resolved the issue.

RMAN Restore (Netbackup)

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN Backup using Netbackup

After Netbackup configuration on the Oracle Server, you can use rman to take backup on tape.

How To Change ASM SYS PASSWORD ?

Things tried:
SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []

Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

ORA-02449 during tablespace drop

SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Using Data Pump

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. The Data Pump is a server based utility vs. the traditional Export / Import which are client based utilities. The Oracle Data Pump is not compatible with the Export / Import functionality. The Oracle Data Pump can also use NETWORK_LINK functionality to move data and metadata from a remote database without a dumpfile using the network technology between the two databases.

Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export job and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.

create a directory object
To create a directory, you must have the DBA role or you must have been granted the CREATE ANY DIRECTORY privilege.

Example (a DBA creates directories on the Windows platform and grants access to user scott):

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs';
GRANT read, write ON DIRECTORY my_dir TO scott;
GRANT read, write ON DIRECTORY my_logdir TO scott;

Example (a normal user with the CREATE ANY DIRECTORY privilege creates directories on the Unix platform - this user automatically has READ and WRITE privilege on that directory):

CONNECT system/manager
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger
CREATE OR REPLACE DIRECTORY my_dir as '/usr/DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as '/usr/logs'; 

Note that the CREATE DIRECTORY statement does not actually create the directory for you on disk. If the directory is invalid, a DataPump job will fail with:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation 

Example(dpexport.bat)

@echo off
cls
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%

expdp 'sys/oracle10g@dupdb as sysdba' dumpfile= dupdb_full_export_%yy%%mm%%dd%.dmp logfile=dupdb_full_export_%yy%%mm%%dd%.log DIRECTORY=DUPDB_FULL_EXPORT_DIR full=Y

expdp 'monitor/monitor' dumpfile=dpdir:exp%U.dmp logfile=prod_full_export20120606.log DIRECTORY=dpdir full=Y cluster=N exclude=statistics parallel=5

Schema Exports/Imports
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

impdp 'sys/syspw as sysdba' directory=dpdir dumpfile=exp%U logfile=IMP

DPProd.log full=y cluster=N parallel=5

Include/Exclude 

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

A single import/export can include multiple references to the parameters, so to export tables, views and some packages we could use either of the following approaches.

INCLUDE=TABLE,VIEW,PACKAGE:"LIKE '%API'"

or

INCLUDE=TABLE
INCLUDE=VIEW
INCLUDE=PACKAGE:"LIKE '%API'"

 Network Exports/Imports (NETWORK_LINK) The NETWORK_LINK parameter identifies a database link to be used as the source for a network export/import. The following database link will be used to demonstrate its use.

CONN / AS SYSDBA
GRANT CREATE DATABASE LINK TO test;

CONN test/test
CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'DEV';

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require the EXP_FULL_DATABASE role granted to them.

expdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR dumpfile=EMP.dmp logfile=expdpEMP.log

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

Monitoring Data Pump

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, 

       s.status, s.username, d.job_name, p.spid, s.serial#, p.pid 

  from v$session s, v$process p, dba_datapump_sessions d

 where p.addr=s.paddr and s.saddr=d.saddr; 

select sid,username ,event,status,state,module,last_call_et

from v$session

where status ='ACTIVE'

and username like 'SYS%'  --user who is importing/exporting

select * from dba_datapump_jobs

where operation ='EXPORT'

select * from dba_datapump_jobs

where operation ='IMPORT'

select * from dba_datapump_sessions

select username, opname,target_desc,sofar,totalwork,message

from v$session_longops

where message like 'SYS%'

waitevent associated with dp
When using data pump you will have a calling session which is the session that you log into the DB with when executing the impdp or expdb utility. Within the DB there will be one or more sessions which are called by this main data pump session;


direct path read

wait for unread message on broadcast channel
Datapump dump file I/O

Note:

You can also include the undocumented parameter METRICS=y to include additional logging information about number of objects and the time it took to process them in the log file.

on and off the ARCHIVELOG mode

1. Turning on ARCHIVELOG mode:

We consider a database that is not in ARCHIVELOG mode and also automatic archival is not enabled. To see the status of the database we can use of the following SQL commands:
SQL> select log_mode from v$database;

Shared Storage

D:\oracle\product\10.2.0\crs\BIN>cluvfy comp ssa -n home-03-db12

Verifying shared storage accessibility

Checking shared storage accessibility...

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk0\Partition-1         home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  OCRCFG                                home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  VOTEDSK1                              home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition3          home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition4          home-03-db12

  Disk Partition                        Sharing Nodes (1 in count)
  ------------------------------------  ------------------------
  \Device\Harddisk2\Partition5          home-03-db12


Shared storage check was successful on nodes "home-03-db12".

Verification of shared storage accessibility was successful.

---------