ORA-02304: invalid object identifier literal and ORA-39083 errors during impdp

Users might receive below error during the import.

ORA-39083: Object type TYPE:"ORCL"."OID_TAB1" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "ORCL"."OID_TAB1" OID 'FB3CF41KK327B011D053F114ABBC878C' AS OBJECT (
className1 VARCHAR2(200),
id1 NUMBER)

Run below query in the database and check for TPYE_ODI exists or not

 
SQL> select OWNER, TYPE_NAME from DBA_TYPES where TYPE_OID='FB3CF41KK327B011D053F114ABBC878C ';

OWNER TYPE_OID
------------------------------------------ --------------------------------
ORCL      FB3CF41KK327B011D053F114ABBC878C

In most cases you will see the ODI is already exists in the database. The ODI already exists, and you cannot create ODI with same name that’s why it failed. You need to follow one of the options.

1. Create the object with new ODI – You can remove the ODI clause from the statement and run again. The database will generate new ODI for the object.

2. Drop the existing TYPE_ODI and reimport - DROP the object and reimport so that object will create with same ODI

3. In “impdp” use the parameter transform=oid:n - Using the parameter during the impdp will import TYPES with new ODI.

I have used all 3 methods based upon different scenarios.

Thanks & Regards,

http://oracleracexpert.com

WARNING: inbound connection timed out (ORA-3136)

The ORA-3136 will be written into alert.log when users fails to provide credentials and cannot authenticate within the set timeout value.

The default value set for below parameters in sqlnet.ora is 60 seconds.

SQLNET.INBOUND_CONNECT_TIMEOUT
INBOUND_CONNECT_TIMEOUT_listener_name

You will see below error in the alert.log file

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.5.0.0.0
Time: 14-OCT-2023 18:06:02
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xx)(PORT=51290))
2023-10-12T16:06:02.222415-07:00
WARNING: inbound connection timed out (ORA-3136)
2023-10-12T16:15:18.866821-07:00

In the above log you can see the client IP and port, you can able to check corresponding entry in the listener log as well. Also, you can see the time stamp of user initiated the connection and time stamp when user got error, it will be more than 60 sec.

The main reason for the error is …

1. When user or application trying to connect using wrong credentials or no attend made under 1 min threshold default value of the instance.

You can able to reproduce the issue by entering wrong credentials

 
SQL> sqlplus HR/xxxx@orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 13 19:31:30 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

$ tail -f orcl_alert.log
Fri Oct 13 19:32:31 2023
WARNING: inbound connection timed out (ORA-3136)

2. If the database has some performance issues or any network delay can cause long time and cannot authenticate user within default time out i.e. 60 seconds

If the database has some load causing the slowness, then increasing SQLNET.INBOUND_CONNECT_TIMEOUT to higher value will help to reduce the errors.

No DB restart required when you make the change in sqlnet.ora but note that it will be applicable to next server process.

3. The server receives the request but cannot be able to authenticate with in default time out i.e. 60 seconds

Find out what causing the delay in authentication if required increase the time out value.

If you are receiving error frequently you can enable tracing using below command
SQL> alter system set events '3136 trace name errorstack level 3';

 
You can trun off tracing using
SQL> alter system set events '3136 trace name context off';

Note that tracing will be generated under USER_DUMP_DEST or BACKGROUND_DUMP_DEST

If you still seeing these warnings I would suggest to raise a ticket with oracle support

Thanks,
https://oracleracexpert.com

Errors ORA-12154, ORA-29003 when connecting to Autonomous Data Warehouse using Django

When connection to Autonomous Data Warehouse Using Django web framework first we come across “ORA-12154” error. That means Django cannot be able to connect to the Oracle Database service name with the settings in settings.py file.

File "C:\django\db\backends\oracle\base.py", line 254, in get_new_connection return Database.connect( cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

First copy the tnsnames.ora into local system and test tnsping.

C:\> tnsping <service Name>

If the tnsping is working fine, then below are the passible reason for ORA-12154 error

• TNS_ADMIN might not be configured or set the right path
• There might be a typo in service name
• Connection details in settings.py might not be correct

We found that TNS_ADMIN is not set to the right path and updating the correct path resolved the issue. But we have received new error

ORA-29003: SSL transport detected mismatched server certificate

Django supports Oracle Database 19c or higher versions but cx_oracle python driver required. We verified that required driver is installed. We try to connect using SQL*PLUS but still receiving same error. That means there is no issue with the settings.

After research we found that 12c client is not supported for new mTLS authentication as per document “ALERT: Action Required for Autonomous Databases (Doc ID 2911553.1)”

We have installed Oracle 19c client and were able to connect without any issues.

Below are the options to resolve the issue

1. Install certified oracle client versions 11.2.0.4.220719 (or later), 18.19 (or later), 19.2 (or later), 21 (base release or later)

2. Update Autonomous database instance to allow both TLS and mTLS authentication.

• Goto Autonomous Database Details page --> Network --> click Edit in the Mutual TLS (mTLS) Authentication field.
• Change the value to allow TLS authentication, deselect Require mutual TLS (mTLS) authentication
• Click Update

3. Use ssl_server_dn_match=no in the connect string (when using 12c client updating the value to “no” didn’t help )

I hope this helps.

Thanks
Satishbabu G, Oracle ACE Pro

https://oracleracexpert.com

Oracle AutoUpgrade with source and Target Database on different servers

AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer) and it has Analyze, Fixup, Deploy and Upgrade processing modes. 

Before you proceed make sure you meet following requirements.

• create config file
• Source Database should be running in original oracle home
• The Database server should be registered in DNS
• JAVA8 required for Autoupgrade to run. Oracle 12c R2 (12.2.0.1) or newer oracle homes have a java version by default.
• If you run AutoUpgrade in batch or script mode use “noconsole” parameter

From Oracle 19c (10.3) autoupgrade.jar file exists by default, for earlier releases you must download latest autoupgrade.jar file from Oracle support.

There are four AutoUpgrade modes and each mode performs different steps

•  Analyze Mode: Setup, Pre-checks.
• Fixups Mode: Setup, Pre-checks, and Pre-fixups.
• Deploy Mode: Setup, Guaranteed Restore Point, Pre-upgrade, Pre-checks, Pre-fixups, Drain,      Database Upgrade, Post-checks, Post-fixups, and Post-upgrade.
• Upgrade Mode: Setup, Database Upgrade, Post-checks, and Post-fixups.

Analyze Mode: This mode runs few checks to see weather database is ready for upgrade. In this mode it only reads data from database to analyze and does not perform any changes.

You can run this step on source database before you setup target.

Run below command to start AutoUpgrade Analyze

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze

Make sure you analyze output files for errors and run fixup on Source database server. You should see SID.html and SID_preupgrade.log files

Note that this mode also generates status files such as status.json, progress.json which can be located under cfgtoollogs/upgrade/auto/status.

Status.json – This file contains High level status of upgrade.
Progress.json – This file has contains status of progress of all upgrades

In case if target database is not on the same server then you must set the source home path in the configuration file, so that Autoupgrade analyze can run on source oracle home.

For example,
upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1 # Source ORACLE_HOME Path
upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path

Fixups Mode : This mode performs all required automated fix up that are required before you start an upgrade. In this mode it creates guaranteed restore point.

Run below command on source Database server to run fixups.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode fixups

Deploy Mode: This mode performs all required actions for an upgrade, which include analyze, fixups, upgrade and post upgrade steps. You will use this mode when source and target Oracle Home’s are on the same server. This mode also creates guaranteed restore point.

Run below command on source Database server to run fixups.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode Deploy

Move the database from source to Target Database server and perform Upgrade mode.

Upgrade Mode: You will run this Autoupgrade mode when you are moving database to Target server or don’t have source Oracle home access. This mode is used only when you are moving to new server. But note that this mode doesn’t create guaranteed restore point and it doesn’t perform the post upgrade steps.

Before you perform upgrade make sure you copy the during_upgrade_pfile_dbname.ora from source to target Oracle Home with default name init<SID>.ora. You can also create the spfile using during_upgrade_pfile_dbname.ora which can be found under temp directory.

SQL> create spfile from pfile=' /home/oracle/autoupgrade/au21/TESTDB/temp/during_upgrade_pfile_testdb.ora';

On Target database server start the database in upgrade mode and run below command to upgrade

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode upgrade

After upgrade make sure you perform post upgrade steps manually

· Copy network files sql.net ora, listener,ora, tnsnames.ora files..etc
· Restart of database in case of RAC
· Remove guaranteed restore point

Thanks & Regards,
https://oracleracexpert.com, Oracle ACE

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class

When launching Database configuration assistant (DBCA) you may encounter this error. The “java.lang.NoClassDefFoundError” error encounter when it could not initialize class “sun.awt.X11.XToolkit”. 

This normally occur when launching Graphic user interface (GUI)

No protocol specified

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:264)

at java.awt.Toolkit$2.run(Toolkit.java:860)

at java.awt.Toolkit$2.run(Toolkit.java:855)

at java.security.AccessController.doPrivileged(Native Method)

at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)

at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)

at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:249)

at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:135)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:348)

at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1879)

at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)

at oracle.install.commons.util.Application.startup(Application.java:976)

at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)

at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)

at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:336)

at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)
at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513)

The main causes of the issue is
1. The DISPLAY variable is NOT set or value is not correct.
2. X Windows is not installed
3. Oracle user not added to ACL (Access Control List)

If launching from server make sure you have any X Windows software installed and install org-x11-apps.x86_64 package

If you are launching from a local workstation, make sure you have XGraphics software such as Exceed, VLC…etc tools installed. Then You can set DISPLAY by using below command

CSH
$ setenv DISPLAY <IP Address of workstation or server>:0

SSH or KSH
$ export DISPLAY=<IP Address of workstation or server>:0

In Some cases, users may face issue when installing Oracle software on Ubuntu or Linux environment.

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

Continue? (y/n) [n] Y

>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-01_12-17-01PM. Please wait ....oracle$:~$ No protocol specified
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit

In this scenario, verify weather JDK installed on the system or not using below command
$ java -version

If not installed then installing required JDK will resolve the issue. If the problem persists, then run below command by logging as root

$ xhost +

Switch back to Oracle user and run the installation.

Thanks,

https://oracleracexpert.com, Oracle ACE Pro

Upgrade Oracle Database using AutoUpgrade Utility

Autoupgrade is used to upgrade one or more databases using command line. Using the tool, you can run pre-upgrade tasks that will provide the fixups, all recommended fixups must be executed before upgrading the database and finish the upgrade by running the post-upgrade tasks. By using this utility you can upgrade hundreds of databases with one command.

It is always recommending that you run AutoUpgrade in Analyze mode before running in Fixup mode. Note that fixup mode can make changes to the source database. The Autoupgrade includes automatic retry and fallback, schedule upgrades and modify or remove the initialization parameters which are deprecated.

AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer)

You need to download AutoUpgrade utility for Databases 12c R2 and 18c. From 19.3 (19c) and later the autoupgrade.jar file exists by default.

Oracle home must contain JAVA to use AutoUpgrade Tool. Always download latest AutoUpgrade.jar file

Create a sample config.txt file for database ugrade

upgrade1.sid=TESTDB                       # ORACLE_SID of source DB
upgrade1.run_utlrp=no                       # Optional. To run utlrp after upgrade select YES
upgrade1.timezone_upg=no                # Optional. To run the timezone upgrade select YES
upgrade1.start_time=now                    # Optional Use “NOW” or specify future upgrade time and date [+XhYm (X hours, Y minutes) | dd/mm/yyyy hh:mm:ss]      
upgrade1.upgrade_node=localhost    # Optional. Default is 'localhost'
upgrade1.target_version=19               # Only required if target database version is 12.2

upgrade1.log_dir= /home/oracle/cfgtoollogs/autoupgrade/TESTDB # Log dir path for the upgrade job

upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1  # Source ORACLE_HOME Path 
upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1   # Target ORACLE_HOME Path 

AutoUpgrade with source and Target Database on same server

• Run below command to start AutoUpgrade Analyze

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze
AutoUpgrade utility launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs pending [0]
------------------- JOBS FINISHED SUCCESSFULLY --------------------
Job 100 for TESTDB

All the logs are under the job id . Pls review all the logs for any errors and information.

You can see the warnings and errors in html file.

 

• Run below command to start deployment of upgrade on same server

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode deploy

AutoUpgrade utility launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

1 databases will be processed

Type 'help' to list console commands

upg>

upg> lsj

+----+-------+---------+---------+-------+--------------+--------+---------------+

|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|

+----+-------+---------+---------+-------+--------------+--------+---------------+

| 102| TESTDB|DBUPGRADE|EXECUTING|RUNNING|21/05/12 15:03|15:35:22|95% Upgraded |

+----+-------+---------+---------+-------+--------------+--------+---------------+

Total jobs 1

Once upgrade is completed verify upg_summary.log for any errors

/u01/home/oracle:DBA>cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Utility 04-30-2021 11:07:0

Database Name: TESTDB

Component Current Full Elapsed Time

Name Status Version HH:MM:SS

Oracle Server UPGRADED 19.10.0.0.0 00:09:54

JServer JAVA Virtual Machine VALID 19.10.0.0.0 00:01:12

Oracle XDK UPGRADED 19.10.0.0.0 00:00:38

Oracle Database Java Packages UPGRADED 19.10.0.0.0 00:00:05

Oracle Text UPGRADED 19.10.0.0.0 00:00:23

Oracle Workspace Manager UPGRADED 19.10.0.0.0 00:00:22

Oracle Real Application Clusters OPTION OFF 19.10.0.0.0 00:00:00

Oracle XML Database UPGRADED 19.10.0.0.0 00:00:51

Oracle Multimedia UPGRADED 19.10.0.0.0 00:01:47

Datapatch 00:01:41

Final Actions 00:01:44

Post Upgrade 00:00:16

Total Upgrade Time: 00:17:31

Database time zone version is 26. It is older than current release time

          zone version 32. Time zone upgrade is needed using the DBMS_DST package.

          Grand Total Upgrade Time: [0d:0h:20m:20s]

If you select upgrade1.timezone_upg=YES then Time zone will be upgraded as part of the Database upgrade. 

AutoUpgrade Stages

AutoUpgrade utility go through series of steps called stages but the actions performed during every stage defined by the “processing mode”. For AutoUpgrade we have Analyze, Fixups, Deploy, and Upgrade processing modes.

AutoUpgrade has the following stages: 

• SETUP: The initial stage in which it starts the job.
• PREUPGRADE: The stage in which it performs readiness for upgrade such as sufficient space
• PRECHECKS: The stage in which it performs pre-checks on source Oracle home to meet requirements for upgrade.
• GRP: Backup database using guaranteed restore point (GRP) before upgrade, this option only available in Enterprise Edition only
• PREFIXUPS: The stage in which it performs preupgrade fixups before upgrade
• DRAIN: The stage during which it shuts down the database to release resources.
• DBUPGRADE: The stage in which it performs the actual upgrade.
• POSTCHECKS: The stage in which it performs post upgrade checks on the target Oracle home before executing any postupgrade fixups.
• POSTFIXUPS: The stage in which it performs processing of postupgrade fixups, timezone upgrade is part of this step.
• POSTUPGRADE: The stage in which in copies or merges the required files from to the target Oracle home for ex:- listener, tns files

There are 2 operational modes during Autoupgrade, i.e. PREPARING and EXECUTING

There are 4 state messages

• RUNNING – AutoUpgrade is still running
• FINISHED - AutoUpgrade is successfully completed
• ERROR – AutoUpgrade has some Errors
• ABORTED - AutoUpgrade aborted response to user request.

Refer useful oracle support notes

• Database Server Upgrade/Downgrade Compatibility Matrix (Doc ID 551141.1)
• Using AutoUpgrade for Oracle Database Upgrades

Thanks & Regards,
https://oracleracexpert.com, Oracle ACE

Create, Drop, Alter Blockchain tables in Oracle 21c

Blockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants. The blockchain tables concept introduced in Oracle 21c and can be backported to 19c using a patch 32431413, but COMPATIBLE parameter must be set to 19.10.0 or late

Create and Drop Blockchain table

When creating block chain you can specify retention period for Blockchain table using “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period and to specify retention period for rows use “NO DELETE” Clause .

Ex:- In below example creates Blockchain_T1 table the table can be dropped until 10 days and rows can be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";

Table level clauses

• NO DROP – The table cannot be dropped. 

• NO DROP UNTIL x DAYS IDLE – Table cannot be dropped when the table is IDLE and no new rows created for specified X number of days or retention period

Where X is the number of days.

Row level clauses

• NO DELETE or NO DELETE LOCKED – The Rows cannot be deleted.

• NO DELETE UNTIL x DAYS AFTER INSERT – the rows cannot be deleted until x number of days they were inserted, the retention setting can be changed using ALTER TABLE command.

• NO DELETE UNTIL x DAYS AFTER INSERT LOCKED – the rows cannot be deleted until x number of days they were inserted , also retention setting cannot be changed until x number of days

Create partition on Blockchain table

In below example creates Blockchain_T1 table with partitions. The table cannot be dropped until 10 days and rows cannot be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(Col3)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-31-2022','mm-dd-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('02-28-2022','mm-dd-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('03-31-2022','mm-dd-yyyy'))
);

You can query USER_TAB_COLS for Blockchain Table details

SQL> SELECT internal_column_id as colid
column_name
data_type,
data_length,
FROM user_tab_cols
WHERE table_name = 'BLOCKCHAIN_T1'
ORDER BY colid;

COLID COLUMN_NAME DATA_TYPE DATA_LENGTH
---------- ------------------------ ------------------------------ -----------

1 Col1 NUMBER 22
2 Col2_ VARCHAR2(48) 48
3 Col3T DATE 7
4 ORABCTAB_INST_ID$ NUMBER 22
5 ORABCTAB_CHAIN_ID$ NUMBER 22
6 ORABCTAB_SEQ_NUM$ NUMBER 22
7 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13
8 ORABCTAB_USER_NUMBER$ NUMBER 22
9 ORABCTAB_HASH$ RAW 2000
10 ORABCTAB_SIGNATURE$ RAW 2000
11 ORABCTAB_SIGNATURE_ALG$ NUMBER 22
12 ORABCTAB_SIGNATURE_CERT$ RAW 16
13 ORABCTAB_SPARE$ RAW 2000

13 rows selected.

You can query {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views to get information about Blockchain Tables

DROP - The below example drops the table if the table has not modified for retention period defined in the Blockchain creation.

SQL> DROP TABLE Blockchain_T1 PURGE;

It is recommended to use PURGE option when dropping a Blockchain table.

 
Note that Blockchain tables cannot be create the root container and application root container.

 In below example Blockchain_T2 table creation failed as it cannot be created in root container

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T2 (Col4 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";

Error report -
ORA-05729: blockchain table cannot be created in root container

ALTER Blockchain Tables : The Blockchain table retention can be modified using ALTER TABLE command

In below example we increased retention for Blockchain_T1 table that it cannot be dropped until table IDLE and no new rows created for 21 days.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 21 DAYS IDLE;

In below example trying to lower retention for Blockchain_T1 table to 16 and the operation failed as retention value cannot be lowered.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 16 DAYS IDLE;

Error report -
ORA-05732: retention value cannot be lowered

You can also increase column length but cannot add or drop column in Blockchain tables.
SQL> ALTER TABLE Blockchain_T1 MODIFY (COL2 VARCHAR2(58));

Table BLOCKCHAIN_T1 altered.

ADD column 

SQL> ALTER TABLE Blockchain_T1 ADD (Col4 varchar2(32));

Error report -
ORA-05715: operation not allowed on the blockchain table

DROP column 
SQL> ALTER TABLE Blockchain_T1 DROP column Col2;

Error report -
ORA-05715: operation not allowed on the blockchain table

DDL and DML on Block chain

In below example we are trying to DELETE, TRUNCATE, UPDATE and MOVE the operation not allowed

DELETE Table

SQL> DELETE FROM Blockchain_T1 where Col1 = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

TRUNCATE Table

SQL> TRUNCATE TABLE Blockchain_T1;

Error report -
ORA-05715: operation not allowed on the blockchain table

UPDATE Table

SQL> UPDATE Blockchain_T1 SET Col2=“Test” WHERE id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

DROP TABLE

SQL> DROP TABLE Blockchain_T1;

Error report -
ORA-05723: drop blockchain table BLOCKCHAIN_T1 not allowed

MOVE Table
SQL> ALTER TABLE Blockchain_T1 move tablespace Blockchain_TBS2 ;

Error report -
ORA-05715: operation not allowed on the blockchain table

Pls refer  Restrictions for Blockchain tables for more details.

Thanks & Regards
https://oracleracexpert.com, Oracle ACE

BlockChain Tables in Oracle 21c

Blockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants.

Oracle 19c introduced Immutable tables concept, that provides protection against unauthorized data modifications. The blockchain tables concept introduced in Oracle 21c and can be backported to 19c using a patch 32431413, but COMPATIBLE parameter must be set to 19.10.0 or late.

These tables are useful to implement Blockchain applications to handle tamper-resistant blockchain transactions with verifiable crypto-secure data management practices. The blockchain tables prevent unauthorized changes or deletion by criminals, hackers and fraud and protect critical company data. Blockchain tables has hidden columns as well and these values are managed by the database.

Blockchain tables and regular tables can be used in queries and transactions, also you can create indexes and partitions.

Blockchain tables hidden columns

Column Name	Data Type	Description
ORABCTAB_INST_ID$	NUMBER (22)	Instance ID of the database instance into which the row is inserted.
ORABCTAB_CHAIN_ID$	NUMBER (22)	Chain ID of the chain, in the database instance, into which the row is inserted.  0 through 31 are valid values.
ORABCTAB_SEQ_NUM$	NUMBER(22)	Sequence number of the row on the chain
ORABCTAB_CREATION_TIME$	TIMESTAMP WITH TIME ZONE	Row created time in UTC format
ORABCTAB_USER_NUMBER$	NUMBER (22)	Database User ID who inserted the row.
ORABCTAB_HASH$	RAW(2000)	Hash value of the row
ORABCTAB_SIGNATURE$	RAW(2000)	User signature of the row
ORABCTAB_SIGNATURE_ALG$	NUMBER(22)	Signature algorithm used to produce the user signature of a signed row.
ORABCTAB_SIGNATURE_CERT$	RAW(16)	GUID of the certificate associated with the signature on a signed row.
ORABCTAB_SPARE$	RAW(2000)	Reserved for future use.

Important Guidelines for Blockchain Tables

• In case of Oracle RAC instance, a block chain table contains 32 chains and chain will have unique combination of instance ID and chain ID. It is recommended to create index on the combination of Instance ID, chain ID and sequence number.

• The SHA2-512 hashing algorithm used to handle hash value

• In case Oracle Data Guard, to avoid data loss consider using Maximum availability or Maximum protection mode

• To specify retention period for Blockchain table use “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period.

• To specify retention period for rows in Blockchain table use “NO DELETE” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period for rows

Restrictions for Blockchain tables

• There are many restrictions when using blockchain tables

• Many datatypes are not supported such as nested table , varray, REF , ROWID, UROWID, LONG, object type, BFILE, XMLType , , , TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

• Point-in-time recovery or flashback database will undo changes on all database objects including blockchain tables.

• Blockchain Retention policies depends on system time and any changes to systems time must be audited.

• Max number of user column allowed are 980

• Blockchain tables doesn’t allow or support below operations

Column level restrictions

o Adding, dropping, and renaming columns

o Dropping partitions

Row level restrictions

o Update or merge rows

o Defining BEFORE ROW triggers that fire for update operations are not allowed.

Table level restrictions

o Truncate table

o Inserting data using parallel DML

o Sharded tables

o During distributed transactions, Inserting data into a blockchain table using Active Data Guard DML redirection is not supported

o Direct-path loading

o Flashback table

o Cannot convert a regular table to a blockchain table or vice versa.

o XA transactions

Database level restrictions

o Export and Import can be done as regular tables, without the system-generated hidden columns.

o Creating blockchain tables in CDB or application root

o Creating Oracle Label Security (OLS) policies

o Using the DBMS_REDEFINITION package for Online redefinition

o Creating Oracle Virtual Private Database (VPD) policies

o When using Transient Logical Standby and rolling upgrades, the DDL and DML on blockchain tables are not replicated and supported

o when using Logical Standby and Oracle GoldenGate, the DDL and DML on blockchain tables succeed on the primary database but are not replicated to standby databases

o Creating Automatic Data Optimization (ADO) policies

Thanks & Regards

https://oracleracexpert.com, Oracle ACE

DBMS_BLOCKCHAIN_TABLE package in Oracle 21c

You can use DBMS_BLOCKCHAIN_TABLE package for maintenance of blockchain table. Using this package you can perform the below tasks.

• Sing a row that is added into the table
• Verify the integrity of rows and hash column.
• Generate signature and signed digest
• Delete rows in a blockchain table that are beyond retention defined.

Here are the few subprograms (procedures and functions) used with DBMS_BLOCKCHAIN_TABLE package

• DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS By using this procedure you can verify the integrity of rows and hash column. You can also verify the Signatures as well

DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
low_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
high_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
instance_id IN NUMBER DEFAULT NULL,
chain_id IN NUMBER DEFAULT NULL,
number_of_rows_verified OUT NUMBER,
verify_signature IN BOOLEAN DEFAULT TRUE);

For ex:-

DECLARE

Rows_Verify NUMBER;
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name => 'BCUSER',
table_name => 'BLOCKCHAIN_T1',
number_of_rows_verified => Rows_Verify);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(Rows_Verify) || ' rows deleted');
END;
/

• DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST – By using this procedure you can generate a signature and signed digest.

DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
signed_bytes IN OUT BLOB,
signed_rows_indexes OUT ORABCTAB_ROW_ARRAY_T,
schema_certificate_guid OUT RAW,
signature_algo IN NUMBER default SIGN_ALGO_DEFAULT)
RETURN RAW;

• DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN - By using this procedure you can verify the integrity of rows created between specified time period X1 and X2

DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN(

signed_bytes_latest IN BLOB,

signed_bytes_previous IN BLOB,

number_of_rows_verified OUT NUMBER);

• DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS - By using this procedure you can delete rows that are beyond retention period

For ex:-

DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,

number_of_rows_deleted OUT NUMBER);

Ex:- The below example deletes the rows beyond the retention period, you can limit the number of rows deletion by using before_timestamp

DECLARE

Del_Rows NUMBER;

DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(

schema_name => 'BCUSER',

table_name => 'BLOCKCHAIN_T1',

before_timestamp => NULL,

number_of_rows_deleted => Del_Rows);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(Del_Rows) || ' rows deleted');

END;

/

• DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE - By using this procedure you can determine the data format for row content to compute the signature.

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

data_format IN NUMBER,

row_data IN OUT BLOB);

• DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH - By using this procedure you can determine the data format for row content to compute hash value

DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

data_format IN NUMBER,

row_data IN OUT BLOB);

• DBMS_BLOCKCHAIN_TABLE.SIGN_ROW – By using this procedure you can add a signature to an existing row.

DBMS_BLOCKCHAIN_TABLE.SIGN_ROW(

schema_name IN VARCHAR2,

table_name IN VARCHAR2,

instance_id IN NUMBER,

chain_id IN NUMBER,

sequence_id IN NUMBER,

hash IN RAW DEFAULT NULL,

signature IN RAW,

certificate_guid IN RAW,

signature_algo IN NUMBER);

• DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512 - – By using this procedure you can validate a signature using specific digital certificate and signature algorithm. Note that Blockchain tables support only DER encoding or X.509 certificates

• Note that DBMS_CRYPTO.HASH function can be used to compute hash value

Blockchain Data byte values:

By using DBMS_TABLE_DATEA package you can get the byte values of row, column

• DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMN - By using this procedure column data in bytes
• DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMNS By using this procedure column data in bytes for set of columns
• DBMS_TABLE_DATA.GET_BYTES_FOR_ROW By using this procedure row data in bytes.

Thanks & Regards
https://oracleracexpert.com, Oracle ACE

Webinar: Oracle Data pump enhancements in Oracle 21c

This Webinar helps you to understand Oracle data pump new features in Oracle 21c and take an advantage of these features.

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

This Webinar covers following Topics.

• Introduction to Oracle Data Pump
• CHECKSUM, CHECKSUM_ALGORITHM
• VERIFY_ONLY and VERIFY_CHECKSUM
• INCLUDE and EXCLUDE in the Same Operation
• Index Compression
• Transportable Tablespace Enhancements
• JSON Data Type Support
• Export/Import from Cloud Object Store
• Q&A

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

Please use Zoom Link to join the Webinar.  

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 

Thanks & Regards,
http://www.oracleracexpert.com

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

I have come across an issue when trying to duplicate a database by connection to Recovery catalog database. You will come across this warning when the user profile has reached PASSWORD_LIFE_TIME value limit and you have entered into PASSWORD_GRACE_TIME value

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

First check the profile that user has been assigned.

SQL> select username , profile from dba_users where username=’RMAN’;
username profile
------------- ---------------
RMAN DEFAULT

Check the current status of the user
SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘RMAN’;
USERNAME ACCOUNT_STATUS
------------- ----------------- --------------------------------
RMAN EXPIRED(GRACE)

Check the RESOUCE_NAME, LIMIT for the Default profile.
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
---------------------------  -------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

As we see password PASSWORD_LIFE_TIME set to 180 days

First you need to change the password for the issue to resolve.
SQL> Alter user rman identified by rman_new_password;

As current password life time set 180 days, You can change the password life time to avoid this issue in future. Either you can set to specific number of days or UNLIMITED.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

You can check the modified resource limit for PASSWORD_LIFE_TIME
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME   LIMIT
---------------------------  -------------------------------------------------
PASSWORD_LIFE_TIME UNLIMITED

Hope this helps

Thanks & Regards

http://oracleracexpert.com, Oracle ACE Pro

Automatic Indexing in Oracle 19c & 21c

The DBA’s are responsible for Index management, which includes monitor index, add, change and remove based upon workload and ad-hoc manner. It will be difficult to decide type of index, when to create, change or drop and measure the impact, so there will be both positive and negative effects.

In Oracle 19c, the automatic indexing feature introduced and it automatically creates, rebuilds, and drops indexes to improve the performance based upon application workload and changes. This helps to optimize the database and improve performance without any user intervention.

The DBMS_AUTO_INDEX package is used for managing the automatic indexing and user can find out the current automatic index configuration by querying CDB_AUTO_INDEX_CONFIG view.

• CONFIGURE Procedure - Configures automatic indexing.
• DROP_AUTO_INDEXES Procedure - Drop the automatically created indexes manually by overriding the retention parameter.
• DROP_SECONDARY_INDEXES Procedure -Deletes all the indexes, except the ones used for constraints
• REPORT_ACTIVITY Function - Generate Report of the automatic indexing operations
• REPORT_LAST_ACTIVITY Function - Generate Report of the last automatic indexing operation

AUTO_INDEX_MODE : The automatic indexing is controlled using the AUTO_INDEX_MODE property, It defines different modes of operation.

• REPORT ONLY: Turn on automatic indexing and new indexes are invisible and not available for SQL operations
• IMPLEMENT: Turn on automatic indexing and new indexes are visible available for SQL operations
• OFF: Turn OFF automatic indexing but does not disable existing auto indexes.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

AUTO_INDEX_SCHEMA Procedure - When automatic indexing enabled all schemas will be used for auto indexes and you can exclude or include any schemas by using AUTO_INDEX_SCHEMA parameter

The below examples adds the SALES schema to AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => FALSE);
end;

The below examples removes the SALES schema from AUTO INDEXES exclusion list

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => 'SALES',
allow => NULL);
end;

The below example removes all the schemas from AUTO INDEXES exclusion list.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_SCHEMA',
parameter_value => NULL,
allow => TRUE);
end;

You can also define retention period that you want auto indexes. The below example sets the retention period of auto indexes to 30 days.

begin
dbms_auto_index.configure(
parameter_name => 'AUTO_INDEX_RETENTION_FOR_AUTO',
parameter_value => '30');
end;

By default automatic indexes created in default permanent tablespace and you can change this by using AUTO_INDEX_DEFAULT_TABLESPACE

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TBS');

You can also allocate percentage of the tablespace can be used for auto indexes. In below example you can use 10%

SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SPACE_BUDGET’, ‘10’);


DROP_AUTO_INDEXES Procedure: Using this procedure you can Drop the automatically created indexes manually by overriding the retention parameter.

The below example will drop a single index in SALES schema and it allow recreate. If you do not want to recreate set the value to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => ‘SALES’,

index_name => '”SYS_AI_45rfg54nxvjcty”',

allow_recreate => TRUE);

end;

/

The below example will drop all auto indexes owned by SALES and will not allow recreate as allow_recreate set to FALSE

begin

dbms_auto_index.drop_auto_indexes(

owner => 'SALES',

index_name => NULL

allow_recreate => FALSE);

end;

/

DROP_SECONDARY_INDEXES Procedure – This procedure deletes all the indexes, except the ones used for constraints

The below example deletes all auto indexes except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes;
end;

The below example deletes all auto indexes from the REGION table in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES', 'REGION');
end;

The below example deletes all auto indexes in the SALES schema except the ones used for constraints

begin
dbms_auto_index.drop_secondary_indexes('SALES');
end;

REPORT_ACTIVITY Function – By using this function you can generate Report of the automatic indexing operations

The below example generates automatic indexing operations report executed in the last 24 hours.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_activity();
end;

or

SQL> select dbms_auto_index.report_activity() from dual;

The below example generates HTML automatic indexing operations report executed in the last 6 hours activity.

SQL> select dbms_auto_index.report_activity(activity_start => systimestamp-0.25, activity_end => systimestamp-1, type => 'HTML') from dual;

REPORT_LAST_ACTIVITY Function -The below example generates report of the last automatic indexing executed in a database.

declare
act_report clob := null;
begin
act_report := dbms_auto_index.report_last_activity();
end;

or

SQL>  select dbms_auto_index.report_last_activity() from dual;

The Oracle 21c comes with an enhancement in Automatic indexing, which helps to reduce the over head of the cursor invalidations during automatic index creation and also added new enhancements to improve query performance.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

DBMS_CLOUD Package in Oracle 21c

In order to work with data in object stores you can either use DBMS_CLOUD package or manually define external table. The package was DBMS_CLOUD package introduced in Oracle Autonomous Database to work with an object store, we can also use in on-prem for versions 19c and 21c but you need to install manually. You can refer Oracle note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) for setup and other details.

You can run below command to create directory, grant access on directory and DBMS_CLOUD package to emp_usr user.

CREATE OR REPLACE DIRECTORY EMP_DIR AS '/oracle/emp_dir';

GRANT EXECUTE ON DBMS_CLOUD TO emp_usr;

GRANT READ, WRITE ON DIRECTORY emp_dir TO emp_usr, C##CLOUD$SERVICE;

Note DBMS_CLOUD is owned by a separate schema C##CLOUD$SERVICE, this user locked by default. Both emp_usr, C##CLOUD$SERVICE user needs to have access.

Credential - You can create credential using the CREATE_CREDENTIAL procedure for your object store.

GRANT CREATE CREDENTIAL TO emp_usr;

BEGIN

DBMS_CLOUD.CREATE_CREDENTIAL (

CREDENTIAL_NAME => 'cloud_obj_cred',

USERNAME => '<username>,

PASSWORD => '<user auth token>'

) ;

END;

/

You can use UPDATE_CREDENTIALS procedure to make changes to the credentials

BEGIN

DBMS_CREDENTIAL.UPDATE_CREDENTIAL(

CREDENTIAL_NAME => 'cloud_obj_cred',

ATTRIBUTE => 'username',

VALUE => 'emp_usr');

END;

/

To drop credential you can use DROP_CREDENTIAL procedure.

BEGIN

DBMS_CLOUD.DROP_CREDENTIAL(CREDENTIAL_NAME => 'cloud_obj_cred’);

END;

/

You can disable and enable the credentials using DISABLE_CREDENTIAL and ENABLE_CREDENTIAL

BEGIN

DBMS_CREDENTIAL.DISABLE_CREDENTIAL('cloud_obj_cred’);

END;

/

BEGIN

DBMS_CREDENTIAL.ENABLE_CREDENTIAL('cloud_obj_cred’);

END;

/

Object store - For managing the objects in the cloud object store, please use below commands

To transfer a file from direct to cloud object store use the PUT_OBJECT procedure

BEGIN

DBMS_CLOUD.PUT_OBJECT (

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

In order to transfer a object from cloud object store to the directory use the GET_OBJECT procedure

BEGIN

DBMS_CLOUD.GET_OBJECT (

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

You can use dbms_cloud.list_objects to lists objects in the object store and use GET_METADATA function In order to get specific object metadata

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx');

SELECT * FROM DBMS_CLOUD.GET_METADATA(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx/emp_file.txt') AS METADATA FROM DUAL;

You can delete objects from the cloud object store using DELETE_OBJECT procedure

 

BEGIN

DBMS_CLOUD.DELETE_OBJECT(

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxx/emp_file.txt');

END;

/

You can delete files from the directory using DELETE_FILE procedure

 

BEGIN

DBMS_CLOUD.DELETE_FILE(

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

To list the files in a directory use LIST_FILES function

SELECT * FROM DBMS_CLOUD.LIST_FILES(DIRECTORY_NAME => 'emp_dir');

To export data into a cloud object store in the required format use EXPORT_DATA procedure

BEGIN

DBMS_CLOUD.EXPORT_DATA (

CREDENTIAL_NAME => 'cloud_obj_cred’,

FILE_URI_LIST => 'https://xxxxxxxxxxx/emp_file.csv',

QUERY => 'select * from emp',

FORMAT => '{"TYPE" : "csv"}');

END;

/

To delete all operation use DELETE_ALL_OPERATIONS procedure

BEGIN

DBMS_CLOUD.DELETE_ALL_OPERATIONS;

END;

/

To delete specific operation use DELETE_OPERATIONS procedure , but you need to provide operation ID

BEGIN

DBMS_CLOUD.DELETE_OPERATION(<id>);

END;

/

If you enter problems with DBMS_CLOUD with the user or role you can test the configuration using the same sample code that was used for the DBMS_CLOUD setup .

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE  

Multitenant: DBCA PDB Remote Clone or Relocate in Oracle 19c

In Oracle 19c, you can clone or relocate a pluggable database (PDB) from one CDB (multitenant container database) to another using the DBCA (Database Configuration Assistant).

The following pre-requisites must met

• The local and remote PDBs must be in the archive log mode and local undo mode.
• It must have the CREATE PLUGGABLE DATABASE privilege in the local CDB root container.
• The database user in the remote PDB that the database link connects must have the CREATE       PLUGGABLE DATABASE, SYSOPER and SESSION privileges.
• The same database options installed on local and remote PDB’s

You can use below query to verify database has local undo mode and archive log mode enabled

SQL> select property_name, property_value from database_properties
where property_name = 'local_undo_enabled';
SQL> select log_mode from v$database;

First, create a user that is used in the database link automatically to connect during the cloning operation. When using DBCA we need to supply the credentials only and no need to create database link

SQL> CREATE USER c##remote_user1 IDENTIFIED BY password CONTAINER=ALL;
SQL> GRANT create session, create pluggable database TO c##remote_user1 CONTAINER=ALL;

You can launch DBCA in silent mode to clone PDB1 from CDB1 as PDB11 in CDB11

$dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName PDB1 -remoteDBConnString CDB1 -remoteDBSYSDBAUserName SYS -remoteDBSYSDBAUserPassword xxxxxxxx -sysDBAUserName sys -sysDBAPassword xxxxxxxx -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxxx -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "PDB11" plugged successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can connect to CDB11 and check the status.

$sqlplus sys@CDB11 as sysdba

SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB11 READ WRITE NO

If you have cloned PDB11 as part of some testing and need to cleanup then use below commands by connecting to CDB11 as SYS

SQL> alter pluggable database PDB11 close;
SQL> drop pluggable database PDB11 including datafiles;


You can also use DBCA to delete the pluggable database that was cloned.

$dbca -silent -deletePluggableDatabase -sourceDB CDB11 -pdbName PDB11

Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
82% complete
94% complete
100% complete
Pluggable database "PDB11" deleted successfully.
Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

You can also use DBCA delete the instance using below command in silent mode

$dbca -silent -deleteDatabase -sourceDB CDB11 -sysDBAUserName sys -sysDBAPassword xxxxxxxxx

The relocatePDB command relocates a PDB from a remote CDB to a local CDB.

$dbca -relocatePDB
-pdbName name_of_the_local_pdb_to_create
-sourceDB database_name_of_the_local_pdb
-remotePDBName name_of_the_remote_pdb_to_relocate
-remoteDBConnString db_connection_string_of_the_remote_pdb
-sysDBAUserName name_of_the_sysdba_user
-sysDBAPassword password_of_the_sysdba_user
-dbLinkUsername name_of_the_dblink_user_of_the_remote_pdb
-dbLinkUserPassword password_of_the_dblink_user_of_the_remote_pdb

Example:

$ dbca -silent -relocatePDB -pdbName PDB11 -sourceDB CDB11  -remotePDBName PDB1 -remoteDBConnString TESTDB -remoteDBSYSDBAUserName sys  -remoteDBSYSDBAUserPassword xxxxxxx  -dbLinkUsername c##remote_user1 -dbLinkUserPassword xxxxxxx

Prepare for db operation

50% complete

Create pluggable database using relocate PDB operation

100% complete

Pluggable database "PDB11" plugged successfully.

Look at the log file "/oracle/cfgtoollogs/dbca/CDB11/PDB11/CDB11.log" for further details.

Refer Oracle documentation for more details and syntax

When running the DBCA in silent mode the outcome will be reported as exit codes. These exit codes helps to identify the command is successful or failed.

Exit Code Description
0  : Command execution successful
6  : Command execution successful but with warnings
-1 : Command execution failed
-2 : Invalid input from user
-4 :  Command canceled by user

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

How to roll forward image copies using RMAN

Recovery Manager (RMAN) is a utility that can be used to backup and recovery your database and it simplifies backing up, restoring, and recovering database or database files

Oracle RMAN can be used to roll forward the copy to the point in time of the most recent level 1 incremental backup by applying level 1 incremental backups to an older image copy. This will help reducing recovery time. To roll forward processes all changes between SCN of original image and incremental backup are applied to the image copy.

When using Roll forward image no need to restore the database instead just switch to updated image copy this will reduce restore time and recovery will be minimal.

run {
allocate channel c1 device type disk;
recover copy of database with tag 'rman_incr_backup';
backup incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

The TAG is used to identify which incremental backups are applying to which image copies.

Note that when the backup runs first time there is no level 0 exists to apply incremental backups. Therefore, it will create level 0 image copy of the database. Going forward it will create level 1 incremental backup and will update previous image copy.

You can also use compression during roll forward image copies using below example

run {
allocate channel c1 device type disk;
recover copy of database with tag ‘rman_incr_backup';
backup as compressed backupset incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

In case if you need to switch the database to image copy you then “shutdown database” and “startup mount”

SQL> shutdown immediate;
SQL> startup nomount;

By using below RMAN command, you can switch the database to image copy

run {
allocate channel c1 device type disk;
switch database to copy;
recover database;
alter database open;
}

When using FRA (Fast Recovery Area) make sure you have has enough space to keep take full image copy database backup.

The Block change tracking will help to improve performance of the incremental backups and you can check the status and enable using below command. 

SQL> select status from v$block_change_tracking;
SQL> alter database enable block change tracking using file '/oracle/oradata/TESTDB/block_change_tracking_file.dbf' reuse;

You can use below command to disable Block change tracking

SQL> alter database disable block change tracking;

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Automatic SQL Tuning Set in Oracle 21c

The SQL tuning sets introduced in Oracle 10g and DBMS_SQLTUNE package used to manage SQL Tuning. You can use SQL tuning sets to group statements into a single object and use as input to tuning tools.

The below command can be used to create a SQL Tuning set

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SQL_Tuning_Set1');

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SQL_Tuning_Set1',
description => 'SQL Tuning Set 1’);
END;

You can use UPDATE_SQLSET procedure to update the attributes of the SQL statements in the SQL tuning Sets

You can use below query to find the SQL Tuning sets owned by the user

SQL> SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM USER_SQLSET;

You can use DELETE_SQLSET procedure to deletes all the statements in SQL Tuning set

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET ( sqlset_name => 'SQL_Tuning_Set1');
END;
/

You can use DROP_SQLSET procedure command the SQL Tuning set using below command

BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQL_Tuning_Set1');
END;
/

You can also transfer the SQL tuning sets following steps create, pack, transfer, and unpack by using use below procedures. You can use datapump or export/import to export/import from source to destination database.

DBMS_SQLTUNE.create_stgtab_sqlset – create a stage
DBMS_SQLTUNE.pack_stgtab_sqlset – To pack SQL tuning sets
DBMS_SQLTUNE.unpack_stgtab_sqlset – To unpack SQL Tuning Sets

In Oracle 11g, further enhancements added to use SQL tuning sets with SQL Performance Analyzer. The DBMS_SQLPA package helps to build and compare two different version of the workload performance.

You can use CREATE_ANALYSIS_TASK to create an analysis task for SQL tuning set or for a single statement or single statement from the workload repository with range of snapshots

You can use below examples…

variable v_task VARCHAR2(64);
variable v_tset_task VARCHAR2(64);

-- SQL Text

EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(

sql_text => select dname, count(*) from dept, emp where dept.deptno = emp.deptno);

-- SQL ID

EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQL_ID => 'cv1d34ds5kdd4');

--Workload repository

exec :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( BEGIN_SNAP => 1, END_SNAP => 2,

SQL_ID => 'cv1d34ds5kdd4');

-- SQL Tuning Set

EXEC :v_tset_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => 'SQL_Tuning_Set1', order_by => 'BUFFER_GETS' );

You can use CANCEL_ANALYSIS_TASK procedure to cancel the task.

EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:v_task);

In Oracle 21c, Automatic SQL tuning automates the entire SQL tuning processes. The automated SQL Tuning sets (ASTS) is a system generated execution plan and performance metrics, it is useful for repairing SQL performance regression when using SQL Plan management.

This feature is introduced in 19c RU 19.7 onwards and available with AWR . You can use ASTS with SQL plan management to implement entire workflow without manual intervention.

You can run below query dba_sqlset_Statements to view statements in ASTS

SQL> SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'SYS_AUTO_STS';

You can use below commands to enable/Disable ASTS

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

CLIENT_NAME => 'ASTS CAPTURE TASK',

OPERATION => NULL,

WINDOW_NAME => NULL);

END;

/

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

CLIENT_NAME => 'ASTS CAPTURE TASK',

OPERATION => NULL,

WINDOW_NAME => NULL);

END;

/

Thanks & Regards

http://oracleracexpert.com