oracle agent - ORACLEAGENT BLOG

Table Level Export & Import in Oracle 19c using Data pump.

DESCRIPTION:

In this Blog, we will learn how to use the Oracle Data Pump Export and Import utility in Oracle 19c database

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, table space, or table-mode export dump file set or another database.

Create a table with an encrypted column and insert rows:

SQL> select * from table01;

ID NAME

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

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

Before start the Export Process, we need to create a Directory which is used to store the dump file.Data Pump is a server-based technology, so it typically deals with directory objects pointing to physical directories on the database server.

Dumpfile: The name of the exported dump file.

Logfile: The name of the directory object that is used to store the log file of the export operation, i.e. the name of the log file

Export the dump file of the table:

Directory Name:MY_Dir

Table Name: Table01

[oracle@oracle19c ~]$ expdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01';

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 08:39:53 2021

Version 19.3.0.0.0

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

Username: data

Password:

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

Starting "DATA"."SYS_EXPORT_TABLE_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for DATA.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/oradata/table01.dmp

Job "DATA"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 25 08:40:05 2021 elapsed 0 00:00:08

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:40:09 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 SQL> conn data/pump

Connected.

Drop the table: 

SQL> drop table table01;

 Table dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:41:28 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

The Data Pump Import utility is started using the impdp command

The following example is a command to import Tables using Dump file.

Dumpfile:table01.dmp

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log;

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:02 2021

Version 19.3.0.0.0

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

Username: data

Password:

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

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 08:42:21 2021 elapsed 0 00:00:14

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:27 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

Finally, verify the contents of the Imported table:

SQL> select * from table01;

ID NAME

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

 1                   aaa

 2                   bbb

 3                   ccc

 4                  ddd

 5                  eee

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Convert Non CDB to CDB in Oracle 12c

Description:-

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database. This is the article which will guide step-by-step Converting Non CDB to CDB in Oracle Database 12c Release.

Step 1: Check the database its properly up and running. shutdown the non-CDB and start it in read-only mode.

Non Container Database Name : NONCDB

Container database Name : ORA_CDB

Non Container Database (Noncdb)

[oracle@localhost ~]$ . ora12c.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:45:36 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

NONCDB READ WRITE

SQL> SELECT name, open_mode FROM v$pdbs;
no rows selected

Container database Name (Ora_cdb)

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:56:55 2021

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8792296 bytes
Variable Size   872417048 bytes
Database Buffers   369098752 bytes
Redo Buffers     7983104 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

ORA_CDB READ WRITE

SQL> col name for a20

SQL> select name, open_mode FROM v$pdbs;

NAME OPEN_MODE

PDB$SEED READ ONLY

CDB READ WRITE

[oracle@localhost ~]$ ps -ef | grep pmon

Step 2: Shutdown the non-CDB database and start it in read-only mode.

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8620224 bytes
Variable Size   872417088 bytes
Database Buffers   369098752 bytes
Redo Buffers     8155136 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Step 3: Create the XML file needed for cdb conversion. the non-cdb database using the DBMS_PDB_DESCRIBE procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/tmp/noncdb.xml’);
END;
/

Step 4:Shutdown the NON-CDB database ( Noncdb).

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 5: Open the cdb database (ORA_CDB)

[oracle@localhost Desktop]$ cd

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 20:36:39 2021

Connected to an idle instance.

SQL> startup

SQL> select name,open_mode,cdb from v$database;

NAME OPEN_MODE CDB

ORA_CDB READ WRITE YES

Step 6: Check the compatibility of PDB in ( ORA_CDB).

SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => ‘/tmp/noncdb.xml’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

Step 7: Check the violations of PDB in (ORA_CDB).

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name=’NONCDB’;

Step 8: Create pluggable database in container db (ORA_CDB).

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY;

create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY

*ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file

‘/u01/app/oracle/oradata/NONCDB/datafile/o1_mf_temp_j0lhxlpy_.tmp’

Note: As tempfile is already there, so mention tempfile reuse tag, to avoid this error.

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY tempfile reuse;

Step 9: Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 10: Startup the PDB and check the open mode PDB.

SQL> alter Pluggable database pdb1 open;

SQL> SELECT name, open_mode FROM v$pdbs;

The non-cdb database has been converted to CDB now successfully.

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Critical Patch Update for January 2021

ORACLE DATABASE PATCHING – JANUARY 2021 UPDATE

The Critical Patch Update for January 2021 was released on January 19, 2021. Oracle strongly recommends applying the patches as soon as possible.

In this document we are going to see how to download and apply Jan 2021 patch.

ORACLE DATABASE PATCHING – JANUARY 2021 UPDATE

Search and download patch from oracle support site.

Go to Dashboard and then Patches and Updates.

Once window will open and it will ask for the product, release and the platform as shown below.

we need to choose 32228578 PATCH

Click on the download option to download the patch. As well as download the read me file. From there you will get an idea to moving forward.

MOVE THE DOWNLOADED PATCH TO ORACLE_HOME LOCATION

UNZIP THE PATCH – To get the updated opatch version

[oracle@trichy PATCH2021]$ unzip p32228578_122010_Linux-x86-64.zip

RUN THE PRE REQ BEFORE APPLYING THE PATCH

[oracle@trichy PATCH2021]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-21-26AM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.

[oracle@trichy 32228578]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_10-23-55AM_1.log

Invoking prereq “checkactivefilesandexecutables”

Prereq “checkActiveFilesAndExecutables” for patch 32228578 passed.

OPatch succeeded.

SHUTDOWN DATABASE AND THE LISTENER.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@trichy PATCH2021]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 20-JAN-2021 10:53:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.22)(PORT=1537)))

The command completed successfully.

GO TO THE PATCH FOLDER AND APPLY THE PATCH.

BEFORE APPLYING PATCH EXPORT THE PATH OF OPATCH FOLDER.

[oracle@trichy ~]$ cd /u01/PATCH2021/32228578/

[oracle@trichy 32228578]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH

[oracle@trichy 32228578]$ opatch version

OPatch Version: 12.2.0.1.19

OPatch succeeded.

[oracle@trichy 32228578]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.19

Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

Verifying environment and performing prerequisite checks…

Prerequisite check “CheckMinimumOPatchVersion” failed.

The details are:

The OPatch being used has version 12.2.0.1.19 while the following patch(es) require higher versions:

Patch 32228578 requires OPatch version 12.2.0.1.23.

Please download latest OPatch from My Oracle Support.

UtilSession failed: Prerequisite check “CheckMinimumOPatchVersion” failed.

Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-20_11-45-30AM_1.log

OPatch failed with error code 73

CHECK THE OPATCH VERSION USING BELOW COMMAND – This patch requires a updated Opatch version.

DOWNLOAD LATEST VERSION OF OPATCH TO APPLY THE RECENT PATCH

[oracle@trichy OPATCH]$ ll

total 119552

drwxr-x—. 15 oracle oinstall 4096 Nov 6 01:20 OPatch

-rwxrw-rw-. 1 oracle oinstall 122415188 Jan 20 11:58 p6880880_200000_LINUX.zip

UNZIP THE OPATCH

[oracle@trichy OPATCH]$ unzip p6880880_200000_LINUX.zip

inflating: OPatch/auto/core/modules/legacyoui/com.oracle.cie.comdev_7.8.5.0.jar

inflating: OPatch/auto/core/modules/legacyoui/com.oracle.cie.gdr-schema_1.8.5.0.jar

inflating: OPatch/auto/core/modules/legacyoui/legacyoui.classpath.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.patch.opatchauto-actions_13.9.4.5.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.automation_13.9.1.0.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.validation_13.9.1.0.jar

creating: OPatch/auto/core/modules/features/

inflating: OPatch/auto/core/modules/features/oracle.glcm.opatchauto.core.binary.classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.opatchauto.core.classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.oplan.core.classpath.jar

inflating: OPatch/auto/core/modules/features/opatchauto-binary-classpath.jar

inflating: OPatch/auto/core/modules/features/oracle.glcm.osys.core.classpath.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.utils_13.9.1.0.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.patch.opatchauto-binary_13.9.4.5.jar

inflating: OPatch/auto/core/modules/com.oracle.glcm.osyscore.model_13.9.1.0.jar

creating: OPatch/auto/dbsessioninfo/

inflating: OPatch/auto/dbsessioninfo/README.txt

creating: OPatch/private/

inflating: OPatch/private/commons-compress-1.19.jar

inflating: OPatch/opatch_env.sh

inflating: OPatch/opatchauto

inflating: OPatch/operr

inflating: OPatch/opatchauto.cmd