Showing posts with label Oracle12cR2 New Feature. Show all posts
Showing posts with label Oracle12cR2 New Feature. Show all posts

oracle 19c : Metrics parameter in expdp

      , ,      No comments   

Here we will be discussing metrics parameter used in expdp or impdp utility, here is the definition of metrics as per oracle, it provides additional information about the export job in the export log file, we will see with the help of an example 

 

METRICS

Report additional job information to the export log file [NO].

 

I performed export with 2 methods

1. Without metrics parameter

2. Without metrics parameter

 

and here is the result of the same

without 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_SCHEMA_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_techno_users1.dmp logfile=expdp_techno_users.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

. . exported "TECHNO_USER"."DEPTS"                       393.4 KB   12002 rows

. . exported "TECHNO_USER"."DUMMY"                       5.085 KB       3 rows

. . exported "TECHNO_USER"."DUMMY1"                      5.085 KB       2 rows

. . exported "TECHNO_USER"."EMPLOYEES"                   393.4 KB   12002 rows

. . exported "TECHNO_USER"."PERSONS"                     425.6 KB   13002 rows

Master table "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TECHNO_USER.SYS_EXPORT_SCHEMA_01 is:

  C:\DOWNLOADS\ARCH\EXPDP_TECHNO_USERS1.DMP

Job "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 2 19:55:23 2022 elapsed 0 00:00:20

 

With 

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_SCHEMA_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log metrics=yes

W-1 Startup took 0 seconds

W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

W-1      Completed 5 TABLE_STATISTICS objects in 0 seconds

W-1 Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

W-1      Completed 1 MARKER objects in 5 seconds

W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds

W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE

W-1      Completed 5 TABLE objects in 13 seconds

W-1 Processing object type SCHEMA_EXPORT/TABLE/COMMENT

W-1 Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ

W-1      Completed 2 AUDIT_OBJ objects in 0 seconds

W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

W-1 . . exported "TECHNO_USER"."DEPTS"                       393.4 KB   12002 rows in 0 seconds using direct_path

W-1 . . exported "TECHNO_USER"."DUMMY"                       5.085 KB       3 rows in 0 seconds using direct_path

W-1 . . exported "TECHNO_USER"."DUMMY1"                      5.085 KB       2 rows in 0 seconds using direct_path

W-1 . . exported "TECHNO_USER"."EMPLOYEES"                   393.4 KB   12002 rows in 0 seconds using direct_path

W-1 . . exported "TECHNO_USER"."PERSONS"                     425.6 KB   13002 rows in 0 seconds using direct_path

W-1      Completed 5 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds

W-1 Master table "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TECHNO_USER.SYS_EXPORT_SCHEMA_01 is:

  C:\DOWNLOADS\ARCH\EXPDP_TECHNO_USERS.DMP

Job "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 2 19:53:15 2022 elapsed 0 00:00:33

 

Here is the conclusion

Export without metrics parameter gives simple log file with much information about objects

Export with metrics parameter give details about a number of objects, time taken by objects to get exported, details about workers.


ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]

      , ,      2 comments   

I was trying to export the schema in my windows PC, it got stuck with below error 

 

C:\Users\shaik\Videos\technodba exp>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log 

 

Export: Release 19.0.0.0.0 - Production on Wed Jun 1 16:13:15 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]

 

On further investigation found that, the directory I had created and pointed out does not have write permission to it, Though I was part of admin group for my PC, it was not allowing oracle to write in C drive.

once I changed directory location, I was able to perform export. 

 

C:\Users\shaik\Videos\technodba exp>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log

 

Export: Release 19.0.0.0.0 - Production on Wed Jun 1 20:54:31 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_SCHEMA_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_techno_users.dmp logfile=expdp_techno_users.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

. . exported "TECHNO_USER"."DEPTS"                       393.4 KB   12002 rows

. . exported "TECHNO_USER"."DUMMY"                       5.085 KB       3 rows

. . exported "TECHNO_USER"."DUMMY1"                      5.085 KB       2 rows

. . exported "TECHNO_USER"."EMPLOYEES"                   393.4 KB   12002 rows

. . exported "TECHNO_USER"."PERSONS"                     425.6 KB   13002 rows

Master table "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TECHNO_USER.SYS_EXPORT_SCHEMA_01 is:

  C:\DOWNLOADS\ARCH\EXPDP_TECHNO_USERS.DMP

Job "TECHNO_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 1 20:54:56 2022 elapsed 0 00:00:20

 

 

C:\Users\shaik\Videos\technodba exp>

12cR2 New Feature -sql prompt "history"

      , ,      No comments   

12cR2 has really great features to use in day to day life for dba. Here we will be discussing one such feature i.e. SQL prompt "history". Many dba's use history command on UNIX prompt to analyze the things or events, Before 12c, this option was not there to see history of sql's you have executed but from 12cR2 onward this is very much possible.
Lets' try this thing out, below is the demonstration 

Check if history parameter is ON/OFF

    SQL> show history; 
    history is OFF

Set history ON

    SQL> set history ON;

    SQL> show history;
    history is ON and set to "100"

Now execute few queries

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE
    --------- --------------------
    TECHCDB   READ WRITE

    SQL> show con_id

    CON_ID
    ------------------------------
    1
    SQL> select count(*) from dba_db_links;

      COUNT(*)
    ----------
             2

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    28-JUL-20

 
Now check the history, you will be able see all the sql's executed.

    SQL> history
      1  show history;
      2  select name,open_mode from v$database;
      3  show con_id
      4  select count(*) from dba_db_links;
      5  select sysdate from dual;

    SQL>

set history OFF

    SQL> set history off
    SQL>
    SQL> history
    SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

this is use of "history" command. Suppose if you forget to turn off history after your work, don't worry it will be only available for that session, it will be automatically turned off.


Categories

Popular Posts