Oracle Checkpoint

Monday, 22 May 2023

How to take a full backup in Oracle

Lets learn today how to take a full backup in Oracle.

We will use recovery manager RMAN utility to take full backup :

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:31:15 2023

Version 21.3.0.0.0

connected to target database: ORCL (DBID=1644501746)

RMAN> backup database;

Starting backup at 22-MAY-23

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qcbpf5_.bkp tag=TAG20230522T232846 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qccstr_.bkp tag=TAG20230522T232846 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T232846_l6qcd93q_.bkp tag=TAG20230522T232846 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

Finished backup at 22-MAY-23

Starting Control File and SPFILE Autobackup at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137540592_l6qcdrcf_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 22-MAY-23

Recovering from a lost control file in Oracle

In this post we learn how to recover from a lost control file.

# First lets take a database backup :

# Lets verify our version of database and environment

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:10:11 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.0 (Maipo)

# Now lets take a backup

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:11:42 2023

Version 21.3.0.0.0

connected to target database: ORCL (DBID=1644501746)

RMAN> backup database;

Starting backup at 22-MAY-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbbz9n_.bkp tag=TAG20230522T231150 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbglpr_.bkp tag=TAG20230522T231150 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 22-MAY-23

channel ORA_DISK_1: finished piece 1 at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2023_05_22/o1_mf_nnndf_TAG20230522T231150_l6qbhp0y_.bkp tag=TAG20230522T231150 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 22-MAY-23

Starting Control File and SPFILE Autobackup at 22-MAY-23

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 22-MAY-23

# Now after deleting any of your control file, try to restart the database :

SQL> startup force

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size 9694088 bytes

Variable Size 377487360 bytes

Database Buffers 683671552 bytes

Redo Buffers 2887680 bytes

ORA-00205: error in identifying control file, check alert log for more info

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Mon May 22 23:20:11 2023

Version 21.3.0.0.0

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp';

Starting restore at 22-MAY-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=422 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORCL/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

Finished restore at 22-MAY-23

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN> recover database ;

Starting recover at 22-MAY-23

Starting implicit crosscheck backup at 22-MAY-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=430 device type=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 22-MAY-23

Starting implicit crosscheck copy at 22-MAY-23

using channel ORA_DISK_1

Finished implicit crosscheck copy at 22-MAY-23

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc

File Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_05_22/o1_mf_s_1137539677_l6qbj5wn_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/oradata/ORCL/redo02.log

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_5_l6qbqvoj_.arc thread=1 sequence=5

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_6_l6qbqz3l_.arc thread=1 sequence=6

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_7_l6qbr0py_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_8_l6qbr3po_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_9_l6qbr6qv_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_05_22/o1_mf_1_10_l6qbr9qt_.arc thread=1 sequence=10

archived log file name=/u01/app/oracle/oradata/ORCL/redo02.log thread=1 sequence=11

media recovery complete, elapsed time: 00:00:01

Finished recover at 22-MAY-23

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:20:51 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> alter database open resetlogs;

Database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

ORCL READ WRITE

Creating a resource manager plan directive in Oracle

In Oracle, Resource plan directives specify how resources are allocated to resource consumer groups or subplans. Each directive can specify several different methods for allocating resources to its consumer group or subplan.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon May 22 23:03:48 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> alter session set container=PDB1

Session altered.

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN => 'DAYTIME',

GROUP_OR_SUBPLAN => 'OLTP',

COMMENT => 'OLTP group',

MGMT_P1 => 75,

SWITCH_GROUP => 'LOW_GROUP',

SWITCH_TIME => 5);

END;

/ 2 3 4 5 6 7 8 9 10

BEGIN

ERROR at line 1:

ORA-29371: pending area is not active

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 2152

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 847

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1867

ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 345

ORA-06512: at line 2

SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA()

PL/SQL procedure successfully completed.

SQL> ed

Wrote file afiedt.buf

1 BEGIN

2 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

3 PLAN => 'DAYTIME',

4 GROUP_OR_SUBPLAN => 'OLTP',

5 COMMENT => 'OLTP group',

6 MGMT_P1 => 75,

7 SWITCH_GROUP => 'LOW_GROUP',

8 SWITCH_TIME => 5);

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()

PL/SQL procedure successfully completed.

Friday, 19 May 2023

Creating and managing Oracle services in single instance Non RAC Systems.

#At first lets try to create a service with srvctl :

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> show parameter uniq

NAME TYPE VALUE

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

db_unique_name string orcl

[oracle@node2 ~]$ srvctl status database -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.

[oracle@node2 ~]$ srvctl add service -s s1 -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.

# This clearly means you cannot manage services in non rac single instance systems with srvctl. The srvctl works with Grid Infrastructure and is the recommended way in Grid Infrastructure. However if you are in non RAC systems then use dbms_service to create and manage services.

Lets create a service now, the version we are using is 21c :

[oracle@node2 dk]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:31:37 2023

Version 21.3.0.0.0

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

BEGIN

DBMS_SERVICE.create_service(

service_name => 'service1',

network_name => 'service1'

);

END;

SQL> /

PL/SQL procedure successfully completed.

# Lets see how to start a service

SQL> exec dbms_service.start_service('service1');

PL/SQL procedure successfully completed.

# Lets see how to stop a service

SQL> exec dbms_service.stop_service('service1');

PL/SQL procedure successfully completed.

# Lets see how to delete a service

SQL> exec dbms_service.delete_service('service1');

BEGIN dbms_service.delete_service('service1'); END;

ERROR at line 1:

ORA-44305: service service1 is running

ORA-06512: at "SYS.DBMS_SERVICE", line 68

ORA-06512: at "SYS.DBMS_SERVICE", line 458

ORA-06512: at line 1

# You cannot delete a running service in Oracle.

# So you must stop it first, lets try it

SQL> exec dbms_service.stop_service('service1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.delete_service('service1');

PL/SQL procedure successfully completed.

# It works now.

# Lets try to connect now :

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

# I am connected in the CDB$ROOT

SQL> show con_name

CON_NAME

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

CDB$ROOT

# Create a user

SQL> create user user1 identified by Oracle123mine ;

create user user1 identified by Oracle123mine

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> create user c##user1 identified by Oracle123mine ;

User created.

SQL> grant connect to c##user1;

Grant succeeded.

[oracle@node2 dk]$ sqlplus c##user1@service1

SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:36:57 2023

Version 21.3.0.0.0

Enter password:

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

Wednesday, 10 May 2023

Automatic Transaction Rollback feature in 23C

The Row lock contention has been a major pain area for developers specially when it comes to releasing locks in case there is an exception thrown by a transaction from an application. The developers in most cases take help from the DBAs to kill their blocking parent session or in essense kill the final blocking session if there is a chain of row locks under the lock tree.

The Automatic Transaction Rollback feature automatically rolls back low-priority transactions that are blocking higher priority transactions from obtaining row locks.

This parameter is one of four initialization parameters that enable you to configure Automatic Transaction Rollback. The other parameters are:

TXN_PRIORITY - Specifies a priority (HIGH, MEDIUM, or LOW) for all transactions in a user session

TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET - Specifies the maximum number of seconds that a MEDIUM priority transaction will wait for a row lock

TXN_AUTO_ROLLBACK_MODE - Specifies whether to enable Automatic Transaction Rollback or test Automatic Transaction Rollback by running in tracking mode

Note: This parameter is available starting with Oracle Database 23c.

Lets see what wait events have been introduced with the 23c version :

SQL> select event#, name, WAIT_CLASS from v$event_name where name like '%TX - row%';

EVENT# NAME WAIT_CLASS

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

340 enq: TX - row lock contention Application

341 enq: TX - row lock (HIGH priority) Application

342 enq: TX - row lock (MEDIUM priority) Application

343 enq: TX - row lock (LOW priority) Application

The parameter txn_auto_rollback_medium_priority_wait_target default value is 2147483647, The TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET specifies the maximum number of seconds that a HIGH priority transaction will wait for a row lock before the Automatic Transaction Rollback feature rolls back a lower priority transaction holding the lock.

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME TYPE VALUE

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

txn_auto_rollback_medium_priority_wa integer 2147483647

it_target

SQL> col NAME For a100

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME TYPE VALUE

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

txn_auto_rollback_medium_priority_wa integer 2147483647

it_target

SQL>

# Now since this is a static parameter, it needs a database restart.

SQL> alter system set txn_auto_rollback_medium_priority_wait_target=50 scope=spfile;

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1608409424 bytes

Fixed Size 10043728 bytes

Variable Size 402653184 bytes

Database Buffers 1191182336 bytes

Redo Buffers 4530176 bytes

Database mounted.

Database opened.

Lets confirm the value :

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME TYPE VALUE

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

txn_auto_rollback_medium_priority_wa integer 50

Session 1 with Medium priority:

SQL> select * from t1;

I I2

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

1 1

2 2

3 3

4 4

5 5

SQL> alter session set TXN_PRIORITY=MEDIUM;

Session altered.

SQL> update t1 set i=11 where i=1;

1 row updated.

Session 2 with High Priority:

SQL> alter session set TXN_PRIORITY=HIGH;

Session altered.

SQL> show parameter TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET

NAME TYPE VALUE

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

txn_auto_rollback_medium_priority_wa integer 50

SQL> select * from t1;

I I2

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

1 1

2 2

3 3

4 4

5 5

SQL> update t1 set i=1001 where i=1;

>>> Hangs here >>>>>

Session 3 DBA session Monitors the wait :

SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';

SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE

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

112 enq: TX - row lock (HIGH priority) Application 0 22 WAITING

SQL> select sid,EVENT,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where event like '%lock%';

SID EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE

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

112 enq: TX - row lock (HIGH priority) Application 0 49 WAITING

Now go to session 1, the one with MEDIUM priority :

SQL> select * from t1;

select * from t1

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 1043

Session ID: 31 Serial number: 10449

Now go to session 2 >> High Priority session :

SQL> update t1 set i=1001 where i=1;

1 row updated.

SQL>

SQL> select * from t1;

I I2

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

1001 1

2 2

3 3

4 4

5 5

# This clearly explains how high priority session takes priority over low priority sessions. Happy 23C #Oracle.

How to create a database in 23C

Creating a database in 23C :

Let us see how we can create a database in Oracle 23c free version for developers :

root@192.9.201.121's password:

Activate the web console with: systemctl enable --now cockpit.socket

Create an account or view all your systems at https://red.ht/insights-dashboard

Last login: Wed May 10 22:03:56 2023

[root@node1-example-com ~]# export DB_PASSWORD=Oracle123mine

[root@node1-example-com ~]# (echo "${DB_PASSWORD}"; echo "${DB_PASSWORD}";) | /etc/init.d/oracle-free-23c configure

Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:

Confirm the password:

Configuring Oracle Listener.

Listener configuration succeeded.

Configuring Oracle Database FREE.

Enter SYS user password:

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

Enter SYSTEM user password:

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

Enter PDBADMIN User Password:

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

Prepare for db operation

7% complete

Copying database files

29% complete

Creating and starting Oracle instance

30% complete

33% complete

36% complete

39% complete

43% complete

Completing Database Creation

47% complete

49% complete

50% complete

Creating Pluggable Databases

54% complete

71% complete

Executing Post Configuration Actions

93% complete

Running Custom Scripts

100% complete

Database creation complete. For details check the logfiles at:

/opt/oracle/cfgtoollogs/dbca/FREE.

Database Information:

Global Database Name:FREE

System Identifier(SID):FREE

Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.

Connect to Oracle Database using one of the connect strings:

Pluggable database: node1-example-com/FREEPDB1

Multitenant container database: node1-example-com

So how do we login to the Oracle 23c Database :

[root@node1-example-com ~]# su - oracle

[oracle@node1-example-com ~]$ export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree

[oracle@node1-example-com ~]$ sqlplus sys/Oracle123mine@//localhost:1521/free as sysdba

bash: sqlplus: command not found...

[oracle@node1-example-com ~]$ ps -ef | grep smon

oracle 11268 1 0 22:49 ? 00:00:00 db_smon_FREE

oracle 11805 11758 0 22:55 pts/2 00:00:00 grep --color=auto smon

[oracle@node1-example-com ~]$ . oraenv

ORACLE_SID = [oracle] ? FREE

The Oracle base has been set to /opt/oracle

[oracle@node1-example-com ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 10 22:56:01 2023

Version 23.2.0.0.0

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 FREEPDB1 READ WRITE NO

Thats it, Enjoy 23c :) :)

Install Oracle 23c database free for Developers

In this article we will learn how to Install Oracle 23c database free for Developers

First, get your downloaded files i.e :

oracle-database-free-23c-1.0-1.el8.x86_64.rpm

oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

The above files can be downloaded from Oracle's web site :

https://www.oracle.com/database/free/

[root@node1-example-com Documents]# ls -ltr

total 1717556

-rwxrw-rw-. 1 root root 1758776440 May 10 22:03 oracle-database-free-23c-1.0-1.el8.x86_64.rpm

[root@node1-example-com Documents]# rpm -ivh oracle-database-free-23c-1.0-1.el8.x86_64.rpm

warning: oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

error: Failed dependencies:

oracle-database-preinstall-23c is needed by oracle-database-free-23c-1.0-1.x86_64

Here if you see i dont have the oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm package.

Lets install this misssing package to ensure the preinstall rpm work is done.

[root@node1-example-com Documents]# ls -ltr

total 1717588

-rwxrw-rw-. 1 root root 1758776440 May 10 22:03 oracle-database-free-23c-1.0-1.el8.x86_64.rpm

-rw-------. 1 root root 30688 May 10 22:07 oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

[root@node1-example-com Documents]# rpm -ivh oracle-database-

oracle-database-free-23c-1.0-1.el8.x86_64.rpm oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

[root@node1-example-com Documents]# rpm -ivh oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

warning: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

error: Failed dependencies:

compat-openssl10 is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

glibc-devel is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

ksh is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

libnsl is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

make is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

sysstat is needed by oracle-database-preinstall-23c-1.0-0.5.el8.x86_64

[root@node1-example-com Documents]# rpm --nodeps -ivh oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm --force

warning: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

Verifying... ################################# [100%]

Preparing... ################################# [100%]

Updating / installing...

1:oracle-database-preinstall-23c-1.################################# [100%]

[root@node1-example-com Documents]# rpm -ivh oracle-database-free-23c-1.0-1.el8.x86_64.rpm

warning: oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY

Verifying... ################################# [100%]

Preparing... ################################# [100%]

Updating / installing...

1:oracle-database-free-23c-1.0-1 ################################# [100%]

root

[INFO] Executing post installation scripts...

[INFO] Oracle home installed successfully and ready to be configured.

To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23c.conf' and then run '/etc/init.d/oracle-free-23c configure' as root.

Oracle Checkpoint

Monday, 22 May 2023

How to take a full backup in Oracle

Recovering from a lost control file in Oracle

Creating a resource manager plan directive in Oracle

Friday, 19 May 2023

Creating and managing Oracle services in single instance Non RAC Systems.

Wednesday, 10 May 2023

Automatic Transaction Rollback feature in 23C

How to create a database in 23C

Install Oracle 23c database free for Developers

Blog Archive

About Me