Oracle 18c Datapump-Schema Level

Oracle 18c Datapump-Schema Level

Description:-

  • In this article we are going to see the Oracle 18c Datapump-Schema level backup
  • The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported.
  • Export all the objects of HR’s schema

Syntax :

expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.log

Let’s start the Demo:-

Pre-steps:-create a directory in the filesystem and creates a directory object in the database and grants privileges to the HR user.

[oracle@testdb ~]$ mkdir –p /u01/app/oracle/datapump
[oracle@testdb ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 Production on Sat Jun 30 05:50:19 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> alter session set container=pdb1;
Session altered.

SQL> create or replace directory test_dir as '/u01/app/oracle/datapump';
Directory created.

SQL> grant read,write on DIRECTORY test_dir to hr;
Grant succeeded.

Demo 1:-Export individual Schema From system Schema

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:08:01 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.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/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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:09:10 2018 elapsed 0 00:01:07

Demo 2:-Export individual Schema From HR Schema

[oracle@testdb ~]$ expdp hr/hr@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr2.dmp logfile=hr2.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:17:39 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr2.dmp logfile=hr2.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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr2.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:18:44 2018 elapsed 0 00:01:02

Demo 3:-Export multiple Schema From system Schema

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr,hari dumpfile=hr_hari.dmp logfile=hr_hari.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:44:42 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr,hari dumpfile=hr_hari.dmp logfile=hr_hari.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/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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
. . exported "HARI"."SAM" 8.539 KB 512 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr_hari.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:45:59 2018 elapsed 0 00:01:14

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

ORA-39173: Encrypted data has been stored unencrypted in dump file set

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:08:01 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.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/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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:09:10 2018 elapsed 0 00:01:07

Solution:-Just add the parameter encryption_password

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr1.dmp logfile=hr1.log encryption_password=hari

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:10:44 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr1.dmp logfile=hr1.log encryption_password=********
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/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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.09 KB 107 rows
. . exported "HR"."LOCATIONS" 8.445 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.203 KB 10 rows
. . exported "HR"."JOBS" 7.117 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.132 KB 27 rows
. . exported "HR"."COUNTRIES" 6.375 KB 25 rows
. . exported "HR"."REGIONS" 5.554 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:11:47 2018 elapsed 0 00:01:01

Oracle 18c Datapump-Table level

Oracle 18c Datapump-Table level

Description:-

  • In this article we are going to see the Oracle 18c Datapump-Table level
  • We can take export single table or multiple tables with the help of TABLES data pump parameter.

Syntax:-

expdp system/password tables=hr.dept,hr.emp dumpfile=data.dmp directory=TEST_DIR
expdp hr/hr tables=dept,emp dumpfile=data.dmp directory=TEST_DIR-same user

Let’s start the Demo:-

Pre-steps:-create a directory in the filesystem and creates a directory object in the database and grants privileges to the HR user.

[oracle@testdb ~]$ mkdir –p /u01/app/oracle/datapump
[oracle@testdb ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 Production on Sat Jun 30 05:50:19 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> alter session set container=pdb1;
Session altered.

SQL> create or replace directory test_dir as '/u01/app/oracle/datapump';
Directory created.

SQL> grant read,write on DIRECTORY test_dir to hr;
Grant succeeded.

Demo 1:-Export individual tables From system Schema

[oracle@testdb human_resources]$ expdp system/Chennai#123@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp.dmp logfile=emp.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:26:28 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp.dmp logfile=emp.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
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:26:58 2018 elapsed 0 00:00:29

Demo 2:-Export individual tables From HR Schema

[oracle@testdb human_resources]$ expdp hr/hr@pdb1 tables=employees directory=TEST_DIR dumpfile=emp1.dmp logfile=emp1.log 

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:30:05 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp1.dmp logfile=emp1.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
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp1.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:30:26 2018 elapsed 0 00:00:20

Demo 3:-Export multiple tables From HR Schema

[oracle@testdb human_resources]$ expdp hr/hr@pdb1 tables=employees,departments directory=TEST_DIR dumpfile=emp3.dmp logfile=emp3.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:38:27 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@pdb1 tables=employees,departments directory=TEST_DIR dumpfile=emp3.dmp logfile=emp3.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
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp3.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:38:43 2018 elapsed 0 00:00:15

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

ORA-28014: cannot drop administrative users Oracle 18c

[oracle@testdb schema]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 18.0.0.0.0 Production on Sat Jun 30 06:14:51 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.1.0.0.0

SQL> drop user hr cascade;
drop user hr cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

SQL> alter session set “_oracle_script”=true;

Session altered.

SQL> drop user hr cascade;

User dropped.

Step by step apply 12c Grid and DB April 2018 PSU to Oracle 12cR1 2 node RAC

Step by step Apply 12c Grid and DB April 2018 PSU to Oracle 12cR1 2 node RACDescription:- 

In this article we are going to see Step by step Apply 12c Grid and DB April 2018 PSU to Oracle 12cR1 2 node RAC

Let start the Demo:-

Download the latest the PSU patch for GRID and ORACLE_HOME :-

Patch 27468957 – Oracle Grid Infrastructure Patch Set Update 12.1.0.2.180417.

The patch 27468957 was the super seeded patch of database PSU patch

Patch 27338041 -Database Patch Set Update 12.1.0.2.180417

Steps to Upgrade the OPATCH UTILITY:-

Step 1:- Check the opatch version and inventory for ORACLE_HOME on both nodes

export ORACLE_SID=prod1
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH

opatch version

opatch lsinventory

Check the opatch version for node 2,

export ORACLE_SID=prod2
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH

Step 2:-Check the opatch version and inventory for GRID HOME on both nodes

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/app/12.1.02/grid
export PATH=/u01/app/12.1.02/grid/OPatch:$PATH

opatch version

opatch lsinventory

Step 3:- Update the opatch utility

Take the backup of OPatch directory from GRID and ORACLE_HOME on both nodes

Unzip the OPatch downloaded zip into GRID and ORACLE_HOME directory

For Grid home:

$ unzip p6880880_121010_LINUX.zip -d /u01/app/oracle/12.1.0.2/grid

$ /u01/app/oracle/12.1.0.2/grid/OPatch/opatch lsinventory

For Database home:

$ unzip  p6880880_121010_Linux-x86-64.zip -d /u01/app/oracle/product/12.1.0.2/db_1

opatch lsinventory

NODE 1 (rac1) :-

Copy patch p6880880_121010_Linux-x86-64.zip to the node 2(rac2) or Copy the unzipped OPatch folder to the node 2(rac2).

NODE 2 (rac2) :-

Check the new OPatch version in both GRID and ORACLE_HOME on node 2,

After upgrade the OPatch utility on both nodes apply the GRID and ORACLE_HOME PSU patches.

Step 4:- Validation of Oracle Inventory 

opatch lsinventory -detail -oh /u01/app/12.1.0.2/grid

To check the GRID HOME inventory :-

Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched

To check the ORACLE_HOME inventory :-

Step 5:- Download and Unzip the April 18 PSU Patch

Step 6:- Check Patch Conflict Detection and Resolution :-

For GRID HOME,

When using OPatch 12.2.0.1.5 or later, the following Opatch Option -ocmrf <ocm response file> does not need to be provided.

Now our current OPatch version for GRID and ORACLE_HOME  is 12.2.0.1.13.

export PATH=/u01/app/12.1.0.2/grid/OPatch:$PATH

/u01/app/12.1.0.2/grid/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/12.1.0.2/grid -analyze

For ORACLE HOME,

export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH

/u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/oracle/product/12.1.0.2/db_1 -analyze

Step 7:- Apply the patch using Opatchauto

opatchauto :-

OPatchauto automatically patch the typical Grid Infrastructure (GI) and RAC home directories with minimal intervention.

In general, when we invoke opatchauto will patch both the GI stack and the database software stack. Since we have mentioned the -oh it will apply the PSU to the specified home.

The main advantage of opatchauto utility was automatically down the CRS and database services and restart the services after apply patching.

To apply a patch using opatchauto,we need to run as a root user.

To patch the GI home and all Oracle RAC database homes of the same version:

# opatchauto apply /u01/27468957

Here,we are going to apply a patch separately for both GRID and ORACLE_HOME in both nodes.

opatchauto for GRID HOME on Node1 :-

export PATH=/u01/app/12.1.0.2/grid/OPatch:$PATH

# /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/12.1.0.2/grid

opatchauto for ORACLE_HOME on Node 1 :-

export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH

# /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/oracle/product/12.1.0.2/db_1

opatchauto for GRID HOME on Node2 :-

export PATH=/u01/app/12.1.0.2/grid/OPatch:$PATH

# /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/12.1.0.2/grid

opatchauto for ORACLE_HOME on Node 2 :-

export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH

# /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatchauto apply /u01/27468957 -oh /u01/app/oracle/product/12.1.0.2/db_1

After  the April 2018 PSU patch is applied on both GRID and ORACLE_HOME successfully.

Check the DBA_REGISTRY_SQLPATCH :-

select BUNDLE_SERIES,PATCH_UID,PATCH_ID,
VERSION,ACTION,STATUS,ACTION_TIME ,DESCRIPTION
from dba_registry_sqlpatch;

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle Rac crsctl and srvctl commands

 

CRSCTL Commands :-

Cluster Related Commands
crs_stat t  Shows HA resource status (hard to read)
crsstat Output of crs_stat t formatted nicely
crsctl check crs CSS,CRS,EVM appears healthy
crsctl stop crs Stop crs and all other services
crsctl disable crs Prevents CRS from starting on reboot
crsctl enable crs Enables CRS start on reboot
crs_stop all Stops all registered resources
crs_start all Starts all registered resources
crsctl stop cluster -all Stops the cluster in all nodes
crsctl start cluster -all Starts the cluster in all nodes

SRVCTL Commands :-

Database Related Commands
srvctl start instance -d <db_name>  -i <inst_name> Starts an instance
srvctl start database -d <db_name> Starts all instances
srvctl stop database -d <db_name> Stops all instances, closes database
srvctl stop instance -d <db_name> -i <inst_name> Stops an instance
srvctl start service -d <db_name> -s <service_name> Starts a service
srvctl stop service -d <db_name> -s <service_name> Stops a service
srvctl status service -d <db_name> Checks status of a service
srvctl status instance -d <db_name> -i <inst_name> Checks an individual instance
srvctl status database -d  <db_name> Checks status of all instances
srvctl start nodeapps -n  <node_name> Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n  <node_name> Stops gsd, vip and listener
srvctl status scan Status of scan listener
srvctl config scan Configuration of scan listener
srvctl status asm Status of ASM instance

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba