Check datapump expdp/impdp job status

 As we learned how to use data pump export/import utilities here, now we will discuss checking the datapump job and taking action on it like terminating the job or resuming, etc.

We will take backup of full database, there is a way to add job name to backup using JOB_NAME parameter, if do not use this parameter, the oracle will automatically generate one job in the database which can be checked under dba_datapump_jobs table or can be checked at server level logs of the expdp/impdp

Below is the command I am using to take export of full database 

expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Here,

I have created directory export_dir pointing to a physical location having read write access, from the command prompt, I executed the command to take export of full database

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 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_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

While this was running, I opened a new command prompt and logged in to the database and checked view dba_datapump_jobs

SQL> select owner_name, job_mode, operation,job_name from dba_datapump_jobs;

OWNER_NAME  JOB_MODE  OPERATION  JOB_NAME

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

TECHNO_USER  FULL      EXPORT     SYS_EXPORT_FULL_01

here I noted job name SYS_EXPORT_SCHEMA_01 and the same can be found from the export command log

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

 

Once you get the job name, use the below command to check the status of the export taken using expdp

 

C:\Users\shaik>expdp userid =techno_user@technopdb attach=SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:45:38 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

Job: SYS_EXPORT_FULL_01

  Owner: TECHNO_USER

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 19C1A2C6616645CDB516452D40951467

  Start Time: Tuesday, 14 June, 2022 20:44:54

  Mode: FULL

  Instance: technodb

  Max Parallelism: 1

  Timezone: +00:00

  Timezone version: 32

  Endianness: LITTLE

  NLS character set: AL32UTF8

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

     TRACE                 0

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Job heartbeat: 6

  Dump File: C:\DOWNLOADS\ARCH\EXPDP_FULL_DB.DMP

    bytes written: 135,168

Here you can see almost every detail of the export job running. Using the help command, you can get a list of activities you can perform

the job can be killed using the kill immediate command

Export> kill job=immediate;

Are you sure you wish to stop this job ([yes]/no): yes

In this way, you can do a lot of things with export attach command. 

 

Read More

Rename pluggable database

There might be a situation when you created a pluggable database with the wrong name then it's not time to worry, you can simply rename it using rename command, in another situation where the client requested to change the name of the pluggable database, then rename command will to amazing work. Here we will discuss renaming oracle PDB

 

Below are the pluggable database present in my system I want to rename technopdb3 to technopdb2 

Below are steps to perform it

The first step is to close the pluggable database 

 

SQL> alter pluggable database technopdb3 close immediate;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     MOUNTED

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

pluggable database technopdb3 has been closed, now open same pluggable database in restricted mode 

 

SQL> alter pluggable database technopdb3 open restricted;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     READ WRITE YES

         5 TECHNOPDB_NEW                  READ WRITE NO

 

now connect to pluggable database using alter session command and execute rename command as mentioned below 

 

SQL> alter session set container=technopdb3;

 

Session altered.

 

SQL> alter pluggable database technopdb3 rename global_name to technopdb2;

 

Pluggable database altered.

 

 

Now close the pluggable database and open in normal mode

 

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB2                     READ WRITE NO

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

In this way, technopdb3 has been renamed to technopdb2

Read More

All about oracle database auditing

As your application and database grow, more and more users get connected to the database, and it becomes difficult to manage all the privileges that individual user has granted. So, there is a feature provided by the oracle called AUDITING.

Users with DBA privileges can do a lot of things with the database, it is important to make sure that users with DBA privileges should not harm the database by any means, in order to do it, the security team or database admin can enable different levels of auditing.

Before enabling any auditing, you need to consider the fact that, this is put some extra effort into the database, so try to push it on a minimal basis.

 

Auditing SYSDBA activity

Based on the parameter AUDIT_SYS_OPERATIONS value, SYSDBA activity is tracked at the operating system level audit trail file. If AUDIT_SYS_OPERATIONS is set to TRUE then every statement executed by the user connected as “as sysdba” or “as sysopr”  is audited. Location of audit trail file can be set and monitored by parameter AUDIT_FILE_DEST

 

Database Auditing

Database auditing is controlled using the parameter AUDIT_TRAIL parameter, there are different values associated with it.

 

NONE (FALSE) – database auditing is disabled

OS – auditing will be recorded at the OS level audit trail and location is controlled by audit_file_dest

DB- auditing will be recorded at database table SYS.AUD$

DB_EXTENDED – saves at database level but includes the SQL statements with bind variables

XML- auditing is done at OS level, formatted with XML tag

XML_EXTENDED- formatted as XML tags, includes SQL statements with bind variables

 

Database auditing can be configured by AUDIT commands,

For example

 SQL> audit update any table; 

Audit succeeded. 

SQL> audit select any table by session; 

Audit succeeded. 

SQL>

 Assume, that few users have “update any table” privilege granted, this can be used to harm the database as well apart from regular work.  In order to record what tables are being updated, you can simply turn on auditing for the same.

By default, auditing will generate one row for each auditing violation.

BY SESSION- one record for each session does not matter how many times it violates (DEFAULT)

BY ACCESS- one record for every violation.

 

Auditing can be enabled on specific objects as well, for example

SQL> audit select on SYS.DBA_USERS whenever successful; 

Audit succeeded. 

SQL> 

This statement generates a record for every successful insertion for the table SYS.DBA_USERS

WHENEVER SUCCESSFUL – records only when the insertion is successful

WHENEVER NOT SUCCESSFUL – records only when the insertion is failed

By default- both conditions are recorded.

 

When AUDIT_TRAIL is set as OS or XML, one can check audit records at OS level audit trail file and when AUDIT_TRAIL is set as DB or a similar one, you can fetch records using SYS.AUD$ or you can use the DBA_AUDIT_TRAIL view. There are around 50 columns available to view in the DBA_AUDIT_TRAIL view.

There are a couple of subset views of DBA_AUDIT_TRAIL and can  be used to narrow down the results

DBA_AUDIT_OBJECT,

DBA_AUDIT_STATEMENT

DBA_AUDIT_SESSION

 

Auditing WITH triggers

Auditing enabled using the audit command will only have a single record for each statement, but it will not have the exact statement used to insert the record. Sometimes you may need to watch out for statements executed,

Database triggers will help to capture the statement based on the condition defined. Assume, there is an update trigger defined on the table, you try to update the table, it will simply generate an audit record and put the row in another table defined in trigger.

 

Fine-Grained Auditing (FGA)

Till now, we have discussed auditing at database level or table level, what if you want to capture auditing for only specific rows in table or views, FGA can help you to achieve this.

FGA can be configured through package DBMS_FGA and add FGA audit policy, need to use ADD_POLICY procedure.

To view records, you need to use the DBA_FGA_AUDIT_TRAIL view, generally FGA auditing can be enabled on rows on which data is critical such as salary or budget or revenue.

DBMS_FGA has a lot of procedures and can be used to add, drop, enable policy, disable policy.

 

SQL> desc dbms_FGA

PROCEDURE ADD_POLICY

PROCEDURE DISABLE_POLICY

PROCEDURE DROP_POLICY

PROCEDURE ENABLE_POLICY 

This is all about the database auditing theory part.

Read More

oracle 19c : Metrics parameter in expdp

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.

Read More

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

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>

Read More

ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.

I was trying to turn on flashback for the oracle database and it was failed with below error 

 

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

 

On further checking I found, database was in noarchivelog mode, this is how we can check archive log mode.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

In order to bring it in archive log mode, I followed steps mentioned here convert database to archive log mode  , once your database is in archive log mode , you can simple turn on flashback without any issues.

Follow below commands to turn on flashback for the database.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            C:\Downloads\arch

Oldest online log sequence     137

Current log sequence           139

SQL>

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select database_name, flashback_on from v$database;

 

DATABASE_NAME        FLASHBACK_ON

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

TECHNODB             YES

 

 

 

Read More

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

While working on database activities, we come across different error, below one of the errors reported while updating parameter LOG_ARCHIVE_DEST_1 in oracle database.

 

SQL> alter system set log_archive_dest_1='C:\Downloads\arch' scope=both;

alter system set log_archive_dest_1='C:\Downloads\arch' scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

 

This is purely due to syntax error, as LOCATION keyword is required before path, so correct syntax will be as below 

alter system set log_archive_dest_1='location=C:\Downloads\arch\';

so I was able to update parameter without any issue

 

SQL> alter system set log_archive_dest_1='location=C:\Downloads\arch\';

 

System altered.

Also read: Statspack installation check

Also read : switching between archive log and noarchivelog

Read More