Apply Patching On Oracle 19c Database Release Update- January 2021

DESCRIPTION

This article we are going to see steps to apply the latest Oracle 19c Database Release Update Patch 32218454

Sep 1 : DOWNLOAD THE PATCH FROM ORACLE SUPPORT

Sep 2 : CHECK THE CURRENT OPTACH VERSION IN THE DATABASE

[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
[oracle@localhost ~]$

Sep 3 : MOVE THE PATCH TO ORACLE HOME LOCATION

[oracle@localhost ~]$ cd Desktop/
[oracle@localhost Desktop]$ ls
LINUX.X64_193000_db_home.zip p32218454_190000_Linux-x86-64.zip VMwareTools-10.0.6-3595377.tar.gz
[oracle@localhost Desktop]$ mv p32218454_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/

NOTE:We need to Upgrade Opatch from “12.2.0.1.17 to 12.2.0.1.23” for applying Patch “32218454”.

Sep 4 : UNZIP THE DOWNLOADED OPATCH

oracle@localhost Desktop]$ mv p6880880_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/
[oracle@localhost Desktop]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ unzip p6880880_190000_Linux-x86-64.zip

Archive: p6880880_190000_LINUX.zip

replace OPatch/emdpatch.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: OPatch/emdpatch.pl
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
inflating: OPatch/oplan/oplan
inflating: OPatch/datapatch

Continues

inflating: OPatch/modules/thirdparty/jackson-databind-2.10.2.jar
inflating: OPatch/modules/thirdparty/jackson-annotations-2.10.2.jar

[oracle@localhost 19.0.0]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost 19.0.0]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@localhost 19.0.0]$

Sep 5 : BEFORE APPLYING THE PATCH CHECK THE STATUS

SQL> col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 6 : SHUTDOWN DATABASE AND LISTENER

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0
[oracle@localhost 19.0.0]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 18:14:07

Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost 19.0.0]$
The command completed successfully

Sep 7 : APPLY RU PATCH ON ORACLE_HOME 19C

[oracle@localhost 19.0.0]$ cd 32218454/
[oracle@localhost 32218454]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/19.0.0
Central Inventory : /u01/app/oracle/product/19.0.0/oraInventory
from : /u01/app/oracle/product/19.0.0//oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32218454
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/19.0.0’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Patching component oracle.network.client, 19.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0…
Patching component oracle.precomp.common, 19.0.0.0.0…
Patching component oracle.precomp.lang, 19.0.0.0.0…
Patching component oracle.jdk, 1.8.0.201.0…
Patch 32218454 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32218454].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log
OPatch succeeded.

Sep 8 : STARTUP THE DATABASE AND LISTENER

[oracle@localhost ~]$ cd scripts/
[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 19:12:28
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 27-JAN-2021 19:12:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>

[oracle@localhost OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.10.0.0.0 Production on Wed Jan 27 19:47:50 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18910_2021_01_27_19_47_50/sqlpatch_invocation.log

Connecting to database…OK
Gathering database info…done
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.10.0.0.0 Release_Update 210108185017: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 27-JAN-21 05.00.33.668766 PM
Adding patches to installation queue and performing prereq checks…done
Installation queue:
No interim patches need to be rolled back
Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.10.0.0.0 Release_Update 210108185017
No interim patches need to be applied
Installing patches…

Sep 9 : AFTER APPLYING RU PATCH,CHECK THE DBA_REGISTRY_SQLPATCH

SQL>col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 9 : USING BELOW QUERY, WE CAN VERIFY DESCRIPTION,STATUS OF APPLIED PATCH
COLUMN action_time FORMAT A10
COLUMN status FORMAT A30
COLUMN description FORMAT A30
SELECT action_time,action,status,description,patch_id from dba_registry_sqlpatch;

Sep 10 : CHECK OPATCH LSINVENTORY AND LIST OF PATCHES APPLIED.

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch lspatches
32218454;Database Release Update : 19.10.0.0.210119 (32218454)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@localhost ~]$

Sep 11 : AFTER THE EXECUTION OF DATAPATCH SHOULD RUN UTLRP.SQL TO CLEAR INVALID OBJECT.

SQL> @?/rdbms/admin/utlrp.sql

CHECK THE ACTION & PATCHID IN DBA_REGISTRY_SQLPATCH

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 RAC Administration and Monitoring Scripts

Oracle RAC Administration and Monitoring Scripts -1

To check clusterware status, execute following command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1
[root@racdb ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
To check cluster status, execute following command.
[root@racdb ~]$ crsctl check cluster 
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@racdb ~]$
To check all cluster resource status, execute following command.
[root@Prodecidbrac01 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details 
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.DBFS_DG.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.RECOC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.asm
ONLINE ONLINE Prodecidbrac01 Started,STABLE
ONLINE ONLINE Prodecidbrac02 Started,STABLE
ora.net1.network
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.ons
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE Prodecidbrac01 169.254.94.159,STABL
E
ora.cvu
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.dbm01.db
1 ONLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.ebys.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.ebytest.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.mgmtdb
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
ora.oc4j
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.repdb.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.scan1.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.scan2.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.scan3.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.Prodeci.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.Prodecidbrac01.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.Prodecidbrac02.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
[root@Prodecidbrac01 ~]#

RAC Cluster Command ( Scripts )

To stop Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl stop crs

 

To start Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl start crs
To disable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb ~]$ crsctl disable crs
To enable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb ~]$ crsctl enable crs
To Query Voting disk location, execute following command. Set ASM profile before executing crsctl command.
[oracle@racdb ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE bd81ab8e27a64fbebf7b6b326c5f614d (/Prod/mapper/HDD_E0_S03_1418288932p1) [DATA]
2. ONLINE 450684e276874ff9bff52d481befa809 (/Prod/mapper/HDD_E0_S15_1420185788p1) [DATA]
3. ONLINE fd1efd6de5204f6bbfe9793a480c517f (/Prod/mapper/HDD_E0_S06_1423843312p1) [DATA]
Located 3 voting disk(s).
To find OCR files location, execute following command.
[root@racdb ~]# cat /etc/oracle/ocr.loc
#Prodice/file +DATA getting replaced by Prodice +DATA/Prodeci-c/OCRFILE/registry.255.923150247
ocrconfig_loc=+DATA/Prodeci-c/OCRFILE/registry.255.923150247
local_only=false
[root@racdb ~]#
To check the status of the Oracle Cluster registry , execute following command.
[root@racdb ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 2412
Available space (kbytes) : 407156
ID : 2022573354
Prodice/File Name : +DATA
Prodice/File integrity check succeeded
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@racdb ~]#
To add voting disk, execute following command. Set ASM profile before executing crsctl command.
crsctl add css votedisk new_vote_disk_path
To Delete voting disk, execute following command. Set ASM profile before executing crsctl command.
crsctl delete css votedisk new_vote_disk_path
To Add voting disk to ASM Disk, execute following command.
crsctl add votedisk asm_disk_group
To Replace or Migrate voting disk, execute following command.
crsctl replace votedisk asm_diskgroup / vote_disk_path
To find OCR backup location, execute following command. Set ASM profile before executing crsctl command.
ocrconfig –showbackup auto
To change OCR Backup location, execute following command.
ocrconfig –backuploc Shared_PATH
To Add OCR files location, execute following command.
ocrconfig –add Location_PATH
To Replace or Change OCR files location, execute following command.
ocrconfig –replace New_PATH
To Repair OCR files location, execute following command. Cluster or Oracle RAC Services should be power off.
ocrconfig –repair –add +DATAC1
There are lots of options used with SRVCTL command, i described them below.
-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Click this link for srvctl commands here.

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

TABLESPACE LEVEL PARAMETER

DESCRIPTION:

In this article, we are going to see  how to export and Import table space using Datapump.

Tablespaces are the logical storage units which are used by the database to store separate objects, such as tables, types, PL/SQL code, and so on. Typically, related objects are grouped together and stored in the same tablespace.

Using expdp export utility of data pump we can export tablespaces. Exporting tablespace is also a way of taking logical backup of the tablespace of your database. Exporting tablespace means only the tables contained in a specified set of tablespace are unloaded along with its dependent objects.

Exporting tablespace means

  • Only the tables contained in a specified set of tablespace are unloaded
  • If a table is unloaded, then its dependent objects are also unloaded
  • Tablespace export unloads both object metadata and Data.

DIRECTORY.

Create a directory anywhere in your system or on your network where expdp export utility can save the exported files such as dump files and log files.

TABLE SPACE:

Using this TABLESPACES parameter we can specify the list of tablespace names which you want to export. For example here I have specified USERS.

[oracle@oracle19c ~]$ expdp directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:22:37 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_TABLESPACE_01":  data/******** directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

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

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

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

Dump file set for DATA.SYS_EXPORT_TABLESPACE_01 is:

  /u01/app/oracle/oradata/tab00.dmp

Job "DATA"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jan 25 09:22:51 2021 elapsed 0 00:00:09

Export activity completed successfully.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:23:37 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> select * from table01;

ID NAME

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

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee
SQL> drop table table01;

Table dropped.

SQL> select * from Table01;

select * from Table01

              *

ERROR at line 1:

ORA-00942: table or view does not exist


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

Version 19.3.0.0.0

Import the tablespaces using the Dumpfile

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

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:28 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=tab00.dmp logfile=tabs00.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 09:24:36 2021 elapsed 0 00:00:03

Import Activity successfully completed.Now we can access the tablespaces.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:51 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> 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

Schema Level Export & Import using Datapump in Oracle19c

DESCRIPTION:

In this tutorial we going to learn how to export & Import schema using expdp & Impdp using data pump in Oracle19cDatabase.

A schema is a collection of a logical structure of data or, database objects owned by a database user and shares the same name as the user.Using expdp export utility you can export any schema of your database or we can also say that using expdp export data pump we can take logical backup of any schema in Oracle database.

DIRECTORY:

create a directory object you can use CREATE DIRECTORY command.

Grant DATAPUMP_EXP_FULL_DATABASE role:

Apart from granting read and write privilege on the directory to the user we also need to grant DATAPUMP_EXP_FULL_DATABASE role to the user who wants to perform the export.

SCHEMA:

DATA

In below mentioned query we are going to export schemas at the values of SCHEMA parameter,

[oracle@oracle19c ~]$ expdp data/pump schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 07:02:14 2021

Version 19.3.0.0.0

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

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

Starting "DATA"."SYS_EXPORT_SCHEMA_01":  data/******** schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.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/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

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/INDEX/INDEX

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

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

Dump file set for DATA.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/oradata/schema.dmp

Job "DATA"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jan 25 07:04:22 2021 elapsed 0 00:01:48

Export utility successfully completed.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 07:08:24 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> drop user data;

User dropped.

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

Version 19.3.0.0.0

Now we are going to see IMPORT activity.

Performing the import of Schemas of your database is as simple as exporting them. Similar to expdp Schema export, we use SCHEMAS parameter to perform the import of the schema. SCHEMAS parameter specifies that user wants to perform Schema-Mode import. Also using Schema parameter helps you in choosing a particular schema from the multiple schema export to import

Executing this impdp data pump import command will import the schema DATA into your database.

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

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 07:09:09 2021

Version 19.3.0.0.0

Username: system

Password:

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

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

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 07:09:33 2021 elapsed 0 00:00:05

Import action completed succesfully.Now we can connect to the schemas.

[oracle@oracle19c ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jan 25 07:09:38 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. 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

Full Database Export in Oracle19c using Datapump

DESCRIPTION:

Using expdp utility provided by data pump we can export data or say unload data or metadata from one database to another. By using this utility we can either export the complete database or a subset of databases such as Database Schema, Table spaces or even individual tables.

DATABASE VERSION:

19.3.0.0.

DIRECTORY:

create a directory by using CREATE DIRECTORY command.

FULL:

This parameter FULL indicates that you want to perform a full database export. This parameter can have YES or NO values. If you set this parameter to YES that means expdp utility will export all the data and metadata of the database.

DUMPFILE:

Using DUMPFILE parameter you specify the names, and optionally, the directory objects of dump files for an export job.

[oracle@oracle19c ~]$ expdp directory=My_Dir  FULL=Y  dumpfile=fulldb.dmp logfile=fulldb.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:35:42 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_FULL_01":  data/******** directory=My_Dir FULL=Y dumpfile=fulldb.dmp logfile=fulldb.log

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

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/RADM_FPTM

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

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

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.070 KB      37 rows

. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181"           136.5 KB     174 rows

. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows

. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows

. . exported "WMSYS"."WM$HINT_TABLE$"                    9.984 KB      97 rows

. . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows

. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.078 KB      11 rows

. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows

. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows

. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows

. . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows

. . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows

. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows

. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows

. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows

. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows

. . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows

. . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows

. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows

. . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows

. . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows

. . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows

. . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows

. . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows

. . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows

. . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows

. . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows

. . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows

. . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows

. . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows

. . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows

. . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows

. . exported "SYS"."AUD$"                                    0 KB       0 rows

. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows

. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows

. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows

. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows

. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows

. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows

. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows

. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows

. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows

. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows

. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows

. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows

. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows

. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows

. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows

. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows

. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows

. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows

. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows

. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows

. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows

. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows

. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows

. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows

. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows

. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows

. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows

. . exported "MDSYS"."RDF_PARAM$"                        6.515 KB       3 rows

. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows

. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows

. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows

. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows

. . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows

. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows

. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows

. . exported "SYS"."SQL$TEXT_DATAPUMP"                   362.5 KB      63 rows

. . exported "SYS"."SQL$_DATAPUMP"                       7.945 KB      63 rows

. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"             72.75 KB      94 rows

. . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows

. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                1.847 MB    3100 rows

. . exported "SYS"."SQLOBJ$_DATAPUMP"                    14.69 KB      94 rows

. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows

. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"               0 KB       0 rows

. . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows

. . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows

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

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

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

Dump file set for DATA.SYS_EXPORT_FULL_01 is:

  /u01/app/oracle/oradata/fulldb.dmp

Job "DATA"."SYS_EXPORT_FULL_01" successfully completed at Mon Jan 25 09:38:24 2021 elapsed 0 00:02:38

 

full export is done successfully.

Restrictions with Full Database export.

  • A full export does not export system schemas that contain Oracle-managed data and metadata.

  • Grants on objects owned by the SYS schema are never exported.

    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

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

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

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

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

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

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

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,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.

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