Oracle Data pump enhancements in Oracle 21c

Oracle 21c offering new enhancements for Oracle data pump and take an advantage of these features

• CHECKSUM, CHECKSUM_ALGORITHM
• VERIFY_ONLY and VERIFY_CHECKSUM
• INCLUDE and EXCLUDE in the Same Operation
• Index Compression
• Transportable Tablespace Enhancements
• JSON Data Type Support

CHECKSUM, CHECKSUM_ALGORITHM
These parameters enables the export to perform checksum validation for each of the dump files, you can enable using CHECKSUM_ALGORITHM or CHECKSUM. In order to use these the COMPATIBLE parameter must be set to 21.0 or higher

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp schemas=testuser DIRECTORY=test_dir LOGFILE=test.log CHECKSUM=YES checksum_algorithm=SHA256

VERIFY_ONLY and VERIFY_CHECKSUM
The VERIFY_ONLY , VERIFY_CHECKSUM parameter uses the checksum to validate dump files during the import. The VERIFY_CHECKSUM and VERIFY_ONLY parameters are mutually exclusive

$ impdp \'/ AS SYSDBA\' dumpfile=test.dmp DIRECTORY=test_dir LOGFILE=test.log verify_checksum=yes

INCLUDE and EXCLUDE in the Same Operation
From Oracle 21c, the Include and exclude objects within the same export or import job

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp schemas=testuser DIRECTORY=test_dir LOGFILE=test.log include=table exclude=statistics

Index Compression
In Oracle database 21c, we can compress indexes while importing using the INDEX_COMPRESSION_CLAUSE and the TRANSFORM parameter

$ impdp \'/ AS SYSDBA\' dumpfile=test.dmp FULL=Y DIRECTORY=test_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" FULL=Y DIRECTORY=test_dir TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"COMPRESS BASIC\" TRANSFORM=INDEX_COMPRESSION_CLAUSE:\"COMPRESS ADVANCED LOW\" EXCLUDE=CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."EMP" 188.08 KB 107 rows
. . imported "TEST"."DEPT" 90.50 KB 90 rows
. . imported "TEST"."SAL" 40.50 KB 38 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Dec 10 09:22:27 2021 elapsed 0 00:00:32

Transportable Tablespace Enhancements
Before Oracle 21c any failure you cannot able to resume transportable tablespace jobs , but now Oracle Data Pump Resumes Transportable Tablespace Jobs and also Parallelizes Metadata Operations using PARALLEL parameter

$ expdp \'/ AS SYSDBA\' dumpfile=test.dmp TRANSPORT_TABLESPACES=testusr DIRECTORY=test_dir TRANSPORT_FULL_CHECK=YES LOGFILE=test.log REUSE_DUMPFILES=YES PARALLEL=2

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=test.dmp TRANSPORT_TABLESPACES= testusr TRANSPORT_FULL_CHECK=YES LOGFILE=test.log REUSE_DUMPFILES=YES PARALLEL=2
ORA-39396: Warning: exporting encrypted data using transportable option without password
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/DOMAIN_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

JSON Data Type Support
Oracle Data Pump enables export and import of Oracle Database native JSON objects, We can also export and import of tables containing the JSON Datatype in full, tablespaces and table modes using Transportable Tablespace

Oracle 21c also offers export/import from Oracle autonomous Database and Cloud Object stores

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

Unregister a Database from RMAN Recovery Catalog

Use UNREGISTER command to remove the metadata from RMAN recovery catalog. You can unregister one or more databases and but note that physical backups are not deleted by this command. You need to use DELETE BACKUP/ DELETE EXPIRED BACKUP commands to remove physical backups

Before you unregister, make sure you connect to recovery catalog and verify the backups. If the backups no longer needed then you can proceed

The below command provides the summary of all backups taken for a Database using RMAN

RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;

You can specify EXPIRED keyword to identify backups not found during a crosscheck
SQL> LIST EXPIRED BACKUP;

You can use below command to delete expired backups
SQL> DELETE EXPIRED BACKUP;

You can use below command to delete all the existing backups going to DEVICE TYPE DISK
RMAN> DELETE BACKUP DEVICE TYPE DISK;

Method 1: In this method, we are connecting to target database and recovery catalog database to unregister. On a primary/standby databases scenario, it removes all metadata associated to primary as well standby.

$ rman

 
RMAN>connect target /
Connected to target database: TEST (DBID=87658657577)

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

or

RMAN> UNREGISTER DATABASE;
Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

When using NOPROMPT, it doesn't ask for confirmation before UNREGISTER.

Method 2: In this method, we are connecting only recovery catalog database to unregister.

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE TEST NOPROMPT;

In case if there is more than one DB in the recovery catalog database then you need to use DBID to unregister.

If you want to unregister backups associated with Standby only then you can use this method to unregister Standby database by setting DBID. In this case, the backups are still usable by other primary or standby databases

RMAN> SET DBID 87658657577;
executing command: SET DBID
database name is "TEST" and DBID is 87658657577

RMAN> UNREGISTER DATABASE TEST NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

Method 3: In this method, we are unresigering the database dbms_rcvcat Package. When using this package you need to provide DB_KEY and DBID.

You can get the DBID, DB_KEY from RC_DATEABASE
SQL> SELECT db_key, dbid FROM rc_database WHERE name = ‘TEST’;

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(3422 , 87658657577);
PL/SQL procedure successfully completed.

This UNREGISTER command cannot be used when target database configured with ZERO data loss recovery appliance. You can use DBMS_RA.DELETE_DB procedure to unregister a database from Recovery Appliance.

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

Convert Partitioned Table to Non-partitioned Table and vice versa

You might come across the situation that you need to convert Partitioned Table to Non-partitioned Table and vice versa.

You can RUN below command to identify weather the Table is partitioned or not

SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
--------------------- ---
EMP YES

I suggest using data pump to convert Partitioned Table to Non-partitioned Table. Below are the steps
Take a backup of the table that you want to convert to NON-Partitioned table

$ expdp SCOTT/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=emp_expdp.log tables=EMP

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 "SCOTT"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . exported "SCOTT"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . exported "SCOTT"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Import the table into TEST schema using PARTITIONS_OPTIONS parameter

$ impdp TEST/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=imp_emp.log remap_schema=SCOTT:TEST partition_options=merge

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10

This command will merge all partitions while importing as single table.

RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
---------------------- ---
EMP NO

DROP PARTATION

If you need to drop the partition use below command

Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;

ADD/MODFY partition

You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table

For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));

SQL> ALTER TABLE EMP ADD PARTITION YEAR_2003 VALUES LESS THAN ( '01-JAN-2004' ) ;

Please refer oracle documentation for detailed PARTITIONS options and methods..etc

Thanks

http://oracleracexpert.com, Oracle ACE

Webinar: Cloning an Oracle Home or Oracle Install

This webinar helps to Clone Oracle Home or Oracle Install while migrating the databases.

Date and time: Dec 15th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• What is Cloning
• When Cloning useful
• Different methods of Cloning
• How to perform Cloning
• References
• Q&A 

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.

You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

SSL Connection Error: SSL is required but the server doesn’t support it.

When users using connecting MYSQL using MySQL Workbench they received following error

By default MySQL Workbench, use SSL and users may receive above error. It is recommended to use certificates to avoid above error. In case, if you do not have the certificates then you can follow below steps to skip the certificate and connect to the MySQL Database

 



Option 1:

• Open MySQL workbench
• Right click on MySQL instance and select "Edit Connection"
• Go to the "SSL" tab under Connection Method
• Choose "If Available" instead of "Required" in the drop-down for the "Use SSL"
• Click the "Test Connection" and you should connect without errors.

Option 2:

• 
  
• Right click on MySQL instance and select "Edit Connection"
• Go to “Advanced” tab and enter "useSSL=0" in the 'Others' tab
• Click the "Test Connection" and you should connect without errors

Option 3:

 MySQL Workbench version 8.0.26 and below doesn’t use SSL Options by default

Thanks & Regards

http://oracleracexpert.com, Oracle ACE