Unveiling DBMS_SEARCH in Oracle 23 AI: The New Era of Database Search

In the world of database management, efficiently locating data is paramount. With the advent of Oracle 23 AI, Oracle has introduced a game-changing feature: `DBMS_SEARCH`. This new feature, designed to simplify and accelerate search operations within Oracle databases, is making waves in the industry. In this blog post, we will dive deep into what `DBMS_SEARCH` is, how it works, and its benefits through a real-world example.

Imagine a global e-commerce company, "ShopEasy," with millions of users, transactions, products, and reviews stored in its Oracle 23ai database. As the business grows, so does the need to quickly retrieve data that powers search functionality on their platform. Users may want to find products based on specific keywords, filter by categories, or even search through product reviews to see what others are saying. The traditional approach would involve writing complex SQL queries and potentially creating multiple indexes. However, with Oracle 23ai's `DBMS_SEARCH` package, things have become much more streamlined.

### What is `DBMS_SEARCH`?

`DBMS_SEARCH` is a new built-in package in Oracle 23 AI that allows you to perform powerful and flexible full-text searches directly within the database. It provides a simplified, high-performance method for searching across multiple tables and columns, offering a unified search interface similar to popular search engines like Elasticsearch but directly integrated within Oracle.

### How Does `DBMS_SEARCH` Work?

Under the hood, `DBMS_SEARCH` leverages AI-powered indexing and advanced algorithms to understand the context of data better. It automatically indexes the specified columns, understands the relationships between tables, and optimizes search queries to provide near-instantaneous results. The package comes with several built-in functions, such as `DBMS_SEARCH.CREATE_INDEX`, `DBMS_SEARCH.SEARCH`, and `DBMS_SEARCH.DROP_INDEX`, allowing developers to define search contexts, execute searches, and manage indexes effortlessly.

For example, to set up a search index across the `PRODUCTS` and `REVIEWS` tables, you can use:

```sql
BEGIN
DBMS_SEARCH.CREATE_INDEX(
index_name => 'product_review_index',
tables => 'PRODUCTS, REVIEWS',
columns => 'product_name, review_text'
);
END;
/
```

With this setup, the `DBMS_SEARCH.SEARCH` function can be used to search for "wireless headphones" across both tables, returning relevant product names and reviews in a single, optimized query:

```sql
SELECT * FROM TABLE(DBMS_SEARCH.SEARCH('product_review_index', 'wireless headphones'));
```

### Benefits of `DBMS_SEARCH`

The benefits of `DBMS_SEARCH` are manifold:

1. **Unified Search Across Multiple Tables**: Unlike traditional methods where each table would require separate indexing and querying, `DBMS_SEARCH` allows for a single, cohesive search across multiple tables and columns.

2. **AI-Powered Optimization**: By leveraging AI, Oracle 23ai intelligently optimizes search queries, reducing latency and boosting performance. This is especially useful for real-time applications like e-commerce, where quick search results are crucial.

3. **Simplified Development and Maintenance**: Developers can set up and manage search contexts with just a few lines of code. There's no need for external search engines or additional infrastructure.

4. **Enhanced User Experience**: For "ShopEasy," `DBMS_SEARCH` directly translates to a better user experience. Users can search for products, filter by reviews, or find popular items effortlessly, driving more engagement and sales.

### A Real-World Impact

For "ShopEasy," implementing `DBMS_SEARCH` led to a 50% reduction in search query latency and simplified their search infrastructure. The customers could instantly find products matching their queries, read reviews, and make purchases, boosting customer satisfaction and revenue.

### Conclusion

`DBMS_SEARCH` in Oracle 23 AI represents a significant leap forward in database search capabilities, bringing the power of AI-driven search directly into the hands of developers. Whether you’re running an e-commerce platform, a financial service, or a healthcare application, `DBMS_SEARCH` offers an efficient, high-performance, and unified way to handle search operations. With its ease of use, powerful performance, and integration with Oracle's robust database ecosystem, `DBMS_SEARCH` is set to become a must-have tool for modern database management.

Vector index creation in Oracle 23ai

As you are aware there is AI embedded in Oracle 23ai powered by Vector Search, you can create vector indexes in Oracle 23ai, To allow vector index creation, you must enable a new memory area stored in the SGA called the Vector Pool. The Vector Pool is a memory allocated in SGA to store Hierarchical Navigable Small World (HNSW) vector indexes and all associated metadata. It is also used to speed up Inverted Flat File (IVF) index creation as well as DML operations on base tables with IVF indexes.

To size the Vector Pool, use the VECTOR_MEMORY_SIZE initialization parameter. You can dynamically modify this parameter at the following levels:

1) At the CDB level VECTOR_MEMORY_SIZE specifies the current size of the Vector Pool. Reducing the parameter value will fail if there is current vector usage.

2) At the PDB level VECTOR_MEMORY_SIZE specifies the maximum Vector Pool usage allowed by a PDB. Reducing the parameter value will be allowed even if current vector usage exceeds the new quota.

3) You can change the value of a parameter in a parameter file in the following ways:

3a) By editing an initialization parameter file. In most cases, the new value takes effect the next time you start an instance of the database.

3b) By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file.

3c) By issuing an ALTER SYSTEM RESET statement to clear an initialization parameter value and set it back to its default value.

Lets see a demo how you can do it :

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.4.0.24.05

SQL> show con_name

CON_NAME

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

FREEPDB1

SQL>  show user

USER is "SYS"

SQL>  show parameter vector_memory_size

NAME                                 TYPE        VALUE

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

vector_memory_size                   big integer 0

SQL> SELECT ISPDB_MODIFIABLE FROM   V$SYSTEM_PARAMETER WHERE  NAME='vector_memory_size';

ISPDB

-----

TRUE

SQL>  ALTER SYSTEM SET vector_memory_size=1G SCOPE=BOTH;

System altered.

SQL> show parameter vector_memory_size

NAME                                 TYPE        VALUE

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

vector_memory_size                   big integer 1G

V$VECTOR_MEMORY_POOL, is the view you can query to monitor the Vector Pool.

Reference : https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/size-vector-pool.html

New Ping command in Oracle 23ai database

 Oracle 23ai offers a very useful command Ping. Lets go deep and understand what it is :

Firstly to make the confusion clear it is just like tnsping, so if you want to test connectivity and see the network reachability for listener, database and so on this is very helpful. As per Oracle officially ping utility : Pings the database or database network listener to check availability. The PING command will fail with an error if either the database or the network listener are unavailable. It indicates that either the network listener or the database needs to be restarted, or that the database connection is not usable and needs to be recreated.

When the PING command is executed without a connect identifier, then a round-trip message is sent through the network listener to the currently connected database. When it is executed with a connect identifier, then a round-trip message is sent to the specified network listener to verify if it is able to handle the database connections.

When i fired just ping it did this to me :

SQL> ping

Ok (1.398 msec)

So it tests the current connection reachability.

Now when i give a wrong name which means i dont have an entry in respective tnsnames.ora it hangs for a moment for obvious reasons and returns this info :

SQL> ping a

>> Hangs here for a while (few seconds)

SP2-1679: Connect identifier (a) was not found.

Help: https://docs.oracle.com/error-help/db/sp2-1679/

Cause:  Connect identifier passed was not found in tnsnames.ora.

Action: Check the connect identifier used in the PING command.

Now lets try to actually ping something whose entry exists in the tnsnames.ora i.e :

SQL> ping FREEPDB1

Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora

Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1)))

Ok (20.312 msec)

SQL> !cat /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

FREE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = FREE)

    )

  )

LISTENER_FREE =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

FREEPDB1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = FREEPDB1)

    )

  )

#Happy learning, thanks

Reference : https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/ping.html#GUID-60C4A1C5-535A-4C9B-88F7-DDE2C198FD6E

Oracle 23ai helpful feature of error details for Oracle users

I was testing the new 23ai free VM shared by Oracle, and i observed very helpful feature of 23ai which shares us the url of the error to read more about it for knowledge. Here is how it works :

So here the table t does not exists, and we get Help page to see about the error and its details and action page

 SQL> select * from t;

select * from t

              *

ERROR at line 1:

ORA-00942: table or view "SYS"."T" does not exist

Help: https://docs.oracle.com/error-help/db/ora-00942/

SQL>  show errordetails

errordetails ON

SQL> set errordetails off

SQL>  select * from t;

 select * from t

               *

ERROR at line 1:

ORA-00942: table or view "SYS"."T" does not exist

SQL> select * from t;

select * from t

              *

ERROR at line 1:

ORA-00942: table or view "SYS"."T" does not exist

# Further the verbose page shows more details like Cause: as well.

SQL> set errordetails verbose

SQL>  select * from t;

 select * from t

               *

ERROR at line 1:

ORA-00942: table or view "SYS"."T" does not exist

Help: https://docs.oracle.com/error-help/db/ora-00942/

Cause:     The specified table or view did not exist, or a synonym

           pointed to a table or view that did not exist.

           To find existing user tables and views, query the

           ALL_TABLES and ALL_VIEWS data dictionary views. Certain

           privileges may be required to access the table. If an

           application returned this message, then the table that the

           application tried to access did not exist in the database, or

           the application did not have access to it.

Action:    Check each of the following:

           - The spelling of the table or view name is correct.

           - The referenced table or view name does exist.

           - The synonym points to an existing table or view.

           If the table or view does exist, ensure that the correct access

           privileges are granted to the database user requiring access

           to the table. Otherwise, create the table.

           Also, if you are attempting to access a table or view in another

           schema, make sure that the correct schema is referenced and that

           access to the object is granted.

Params: 1) object_name: The table or view name specified as

                        SCHEMA.OBJECT_NAME, if one is provided.

                        Otherwise, it is blank.

How to fix ORA-01017 in 23ai Oracle

 As you know Oracle has announced 23ai as a big announcement with so many features more than 300 for developers and Oracle users, here is the post on How to fix ORA-01017 in 23ai Oracle for the VM which Oracle provides to developer for free .

[oracle@localhost ~]$ ps -ef | grep smon

oracle      1738       1  0 17:27 ?        00:00:00 db_smon_FREE

oracle      4398    4334  0 17:37 pts/3    00:00:00 grep --color=auto smon

[oracle@localhost ~]$ . oraenv

ORACLE_SID = [FREE] ? FREE

The Oracle base remains unchanged with value /opt/oracle

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat May 25 17:37:30 2024

Version 23.4.0.24.05

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

ERROR:

ORA-01017: invalid credential or not authorized; logon denied

Help: https://docs.oracle.com/error-help/db/ora-01017/

[oracle@localhost ~]$ sqlplus sys@localhost:1521/FREEPDB1 as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat May 25 17:38:24 2024

Version 23.4.0.24.05

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

Enter password:

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.4.0.24.05

So the solution is, the credentials must be used for FREEPDB1 as mentioned in the Oracle portal . Here is the link for more info to get started with 23ai https://www.oracle.com/database/free/get-started/

Startup Modes in Oracle Database

 In this blog post we will learn Startup Modes in Oracle Database

There are 3 states in which Oracle instance can be started i.e nomount, mount and open. Remember you can go directory to open from scratch i.e when database is shutdown and you can also go directly to mount and as always you can go to mount from nomount or you can go to open from mount as well. Its like climbing stairs in order.

1) First mode is nomount, in this mode only spfile/parameter is read and Oracle instance SGA memory is allocated :- Lets see :

Verify database instance is down :

[oracle@node2 ~]$ ps -ef  | grep smon

oracle     3597   2134  0 11:49 pts/0    00:00:00 grep --color=auto smon

Now lets start instance in nomount mode first as it is the first stage,

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 28 11:49:10 2023

Version 21.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             469762048 bytes

Database Buffers          591396864 bytes

Redo Buffers                2887680 bytes

SQL> select status from v$instance;

STATUS

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

STARTED

#Here  started means nomount mode

2) Now lets go to mount mode :

In this mode controlfile of Oracle is read but physical existence of datafiles is not checked.

SQL> alter database mount;

Database altered.

SQL> select status from v$instance;

STATUS

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

MOUNTED

3) In open mode datafiles existence is also verified and if they need recovery these things are checked during mount to open phase.

SQL>  alter database open;

Database altered.

SQL> SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ WRITE

Now note this is read write mode, means application can do writes and reads a typical mode of Oracle primady database, you can also open in read only mode the instance instead of read write. Lets see how we can do but note you need to shutdown first the database in order to come back to read only. Lets see if we can try and do it :

SQL> SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ WRITE

SQL>  alter database open read only;

 alter database open read only

*

ERROR at line 1:

ORA-01531: a database already open by the instance

# Lets shutdown now :

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

# Startup now to mount mode , because you cannot alter database from nomount to open mode, lets see :

ORACLE instance shut down.

SQL> SQL>

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             469762048 bytes

Database Buffers          591396864 bytes

Redo Buffers                2887680 bytes

SQL>  alter database open read only;

 alter database open read only

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>  select status from v$instance;

STATUS

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

OPEN

SQL>  select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ ONLY

# Also with a single short cut startup force you can shutdown abort and startup in read write mode in a single shot , whichever mode you are currently in , lets see we can do it :

SQL> startup force

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             469762048 bytes

Database Buffers          591396864 bytes

Redo Buffers                2887680 bytes

Database mounted.

Database opened.

SQL>  select status from v$instance;

STATUS

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

OPEN

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

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

PRIMARY          READ WRITE

Alert log contents :

SQL> select value from v$diag_info;

VALUE

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

TRUE

/u01/app/oracle

/u01/app/oracle/diag/rdbms/orcl/orcl

/u01/app/oracle/diag/rdbms/orcl/orcl/trace

/u01/app/oracle/diag/rdbms/orcl/orcl/alert

/u01/app/oracle/diag/rdbms/orcl/orcl/incident

/u01/app/oracle/diag/rdbms/orcl/orcl/cdump

/u01/app/oracle/diag/rdbms/orcl/orcl/hm

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4693.trc

0

0

VALUE

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

/u01/app/oracle/product/21.3.0

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/attention_orcl.log

13 rows selected.

SQL> exit

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

Version 21.3.0.0.0

[oracle@node2 ~]$ tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

2023-05-28T12:10:24.990363+05:30

CJQ0 started with pid=66, OS id=4991

Completed: ALTER DATABASE OPEN

2023-05-28T12:10:28.316040+05:30

Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter

PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window

PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

2023-05-28T12:10:43.793248+05:30

Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.

^C

[oracle@node2 ~]$ tail -200f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

2023-05-28T12:09:01.079236+05:30

ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid

ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

Starting background process PMON

2023-05-28T12:09:01.087835+05:30

PMON started with pid=2, OS id=4579

Starting background process CLMN

2023-05-28T12:09:01.096427+05:30

CLMN started with pid=3, OS id=4583

Starting background process PSP0

2023-05-28T12:09:01.105050+05:30

PSP0 started with pid=4, OS id=4587

Starting background process VKTM

2023-05-28T12:09:01.114083+05:30

VKTM started with pid=5, OS id=4591 at elevated (RT) priority

2023-05-28T12:09:01.114836+05:30

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Starting background process GEN0

2023-05-28T12:09:01.122772+05:30

GEN0 started with pid=6, OS id=4597

Starting background process MMAN

2023-05-28T12:09:01.131288+05:30

MMAN started with pid=7, OS id=4601

Starting background process GEN1

Starting background process GEN2

Starting background process VOSD

Starting background process DIAG

Starting background process OFSD

Starting background process DBRM

Starting background process VKRM

Starting background process SVCB

Starting background process PMAN

Starting background process DIA0

Starting background process DBW0

Starting background process LGWR

Starting background process CKPT

Starting background process SMON

Starting background process SMCO

Starting background process RECO

Starting background process LREG

Starting background process PXMN

Starting background process FENC

Starting background process MMON

Starting background process MMNL

2023-05-28T12:09:01.189362+05:30

GEN1 started with pid=9, OS id=4607_4609

2023-05-28T12:09:01.234103+05:30

GEN2 started with pid=11, OS id=4610

2023-05-28T12:09:01.260151+05:30

DIAG started with pid=13, OS id=4617

2023-05-28T12:09:01.262894+05:30

VOSD started with pid=12, OS id=4613

2023-05-28T12:09:01.304813+05:30

OFSD started with pid=14, OS id=4620_4622

2023-05-28T12:09:01.306459+05:30

Oracle running with ofslib:'Oracle File Server Library' version=2 inc=1

2023-05-28T12:09:01.349021+05:30

VKRM started with pid=17, OS id=4626

2023-05-28T12:09:01.350636+05:30

DBRM started with pid=16, OS id=4623

2023-05-28T12:09:01.377093+05:30

SVCB started with pid=18, OS id=4629

2023-05-28T12:09:01.435357+05:30

DIA0 started with pid=20, OS id=4634

2023-05-28T12:09:01.439370+05:30

PMAN started with pid=19, OS id=4631

2023-05-28T12:09:01.464040+05:30

DBW0 started with pid=21, OS id=4637

2023-05-28T12:09:01.479582+05:30

CKPT started with pid=23, OS id=4644

2023-05-28T12:09:01.481077+05:30

LGWR started with pid=22, OS id=4640 at elevated (RT) priority

2023-05-28T12:09:01.489370+05:30

SMCO started with pid=25, OS id=4654

2023-05-28T12:09:01.489751+05:30

SMON started with pid=24, OS id=4649

2023-05-28T12:09:01.497269+05:30

RECO started with pid=26, OS id=4657

2023-05-28T12:09:01.510576+05:30

PXMN started with pid=28, OS id=4662

2023-05-28T12:09:01.512162+05:30

LREG started with pid=27, OS id=4659

2023-05-28T12:09:01.526890+05:30

MMON started with pid=30, OS id=4668

2023-05-28T12:09:01.528532+05:30

FENC started with pid=29, OS id=4665

2023-05-28T12:09:01.542807+05:30

MMNL started with pid=31, OS id=4671

2023-05-28T12:09:01.543530+05:30

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

Starting background process TMON

2023-05-28T12:09:01.605236+05:30

TMON started with pid=42, OS id=4691

RACD notifier running level=0

ORACLE_BASE from environment = /u01/app/oracle

2023-05-28T12:09:01.652008+05:30

ALTER DATABASE   MOUNT

2023-05-28T12:09:01.666269+05:30

Successful mount of redo thread 1, with mount id 1665802461

2023-05-28T12:09:01.666479+05:30

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

2023-05-28T12:09:01.725771+05:30

ALTER DATABASE OPEN

Smart fusion block transfer is disabled:

  instance mounted in exclusive mode.

2023-05-28T12:09:01.729878+05:30

Crash Recovery excluding pdb 2 which was cleanly closed.

2023-05-28T12:09:01.729920+05:30

Crash Recovery excluding pdb 3 which was cleanly closed.

Endian type of dictionary set to little

Starting background process ARC0

2023-05-28T12:09:01.740515+05:30

ARC0 started with pid=44, OS id=4695

2023-05-28T12:09:01.752033+05:30

Redo log for group 3, sequence 21 is not located on DAX storage

Thread 1 opened at log sequence 21

  Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ORCL/redo03.log

Successful open of redo thread 1

2023-05-28T12:09:01.755723+05:30

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Stopping change tracking

Starting background process ARC1

2023-05-28T12:09:01.838258+05:30

ARC1 started with pid=48, OS id=4703

Starting background process ARC2

Undo initialization recovery: Parallel FPTR complete: start:2097012 end:2097013 diff:1 ms (0.0 seconds)

Undo initialization recovery: err:0 start: 2097012 end: 2097013 diff: 1 ms (0.0 seconds)

2023-05-28T12:09:01.873465+05:30

ARC2 started with pid=49, OS id=4705

Starting background process ARC3

2023-05-28T12:09:01.917247+05:30

ARC3 started with pid=50, OS id=4707

[4693] Successfully onlined Undo Tablespace 2.

Undo initialization online undo segments: err:0 start: 2097013 end: 2097094 diff: 81 ms (0.1 seconds)

Undo initialization finished serial:0 start:2097012 end:2097099 diff:87 ms (0.1 seconds)

Database Characterset is AL32UTF8

No Resource Manager plan active

Starting background process RCBG

2023-05-28T12:09:02.307179+05:30

RCBG started with pid=52, OS id=4711

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process AQPC

2023-05-28T12:09:02.522697+05:30

AQPC started with pid=53, OS id=4713

PDB$SEED(2):Pluggable database PDB$SEED opening in read only

PDB$SEED(2):Autotune of undo retention is turned on.

PDB$SEED(2):Endian type of dictionary set to little

PDB$SEED(2):Undo initialization finished serial:0 start:2097868 end:2097868 diff:0 ms (0.0 seconds)

PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8

2023-05-28T12:09:03.056673+05:30

PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)

PDB$SEED(2):Opening pdb with no Resource Manager plan active

PDB1(3):Pluggable database PDB1 opening in read write

PDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18

PDB1(3):Autotune of undo retention is turned on.

PDB1(3):Endian type of dictionary set to little

PDB1(3):Undo initialization recovery: Parallel FPTR complete: start:2098719 end:2098722 diff:3 ms (0.0 seconds)

PDB1(3):Undo initialization recovery: err:0 start: 2098719 end: 2098723 diff: 4 ms (0.0 seconds)

PDB1(3):[4693] Successfully onlined Undo Tablespace 2.

PDB1(3):Undo initialization online undo segments: err:0 start: 2098723 end: 2098868 diff: 145 ms (0.1 seconds)

PDB1(3):Undo initialization finished serial:0 start:2098719 end:2098874 diff:155 ms (0.2 seconds)

PDB1(3):Database Characterset for PDB1 is AL32UTF8

2023-05-28T12:09:04.092265+05:30

PDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)

2023-05-28T12:09:04.107703+05:30

 :

QPI: opatch file present, opatch

 :

QPI: qopiprep.bat file present

2023-05-28T12:09:04.357949+05:30

Using default pga_aggregate_limit of 2048 MB

2023-05-28T12:09:04.601138+05:30

PDB1(3):Opening pdb with no Resource Manager plan active

Pluggable database PDB1 opened read write

2023-05-28T12:09:06.842646+05:30

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

Dumping current patch information

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

No patches have been applied

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

2023-05-28T12:09:07.447556+05:30

db_recovery_file_dest_size of 13896 MB is 34.33% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

2023-05-28T12:10:24.982897+05:30

Starting background process CJQ0

2023-05-28T12:10:24.990363+05:30

CJQ0 started with pid=66, OS id=4991

Completed: ALTER DATABASE OPEN

2023-05-28T12:10:28.316040+05:30

Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter

PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window

PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

2023-05-28T12:10:43.793248+05:30

Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.

# You learnt the 3 modes of Oracle in this post.

Starting Oracle processes via shell script

 In this post we will learn how to start Oracle processes via shell script

Lets write a script to start listener and then the database :

[oracle@node2 ~]$ cat start.sh

ORACLE_SID=orcl

ORAENV_ASK=NO

. oraenv

lsnrctl stop

lsnrctl start

sqlplus -s /nolog  <<EOF

connect / as sysdba

startup

exit;

EOF

>> Now lets execute the script :

[oracle@node2 ~]$ ./start.sh

The Oracle base remains unchanged with value /u01/app/oracle

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-MAY-2023 11:41:41

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.example.com)(PORT=1521)))

The command completed successfully

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-MAY-2023 11:41:41

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/21.3.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 21.0.0.0.0 - Production

System parameter file is /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/node2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.example.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production

Start Date                28-MAY-2023 11:41:41

Uptime                    0 days 0 hr. 0 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/node2/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.example.com)(PORT=1521)))

The listener supports no services

The command completed successfully

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             448790528 bytes

Database Buffers          612368384 bytes

Redo Buffers                2887680 bytes

Database mounted.

Database opened.

>> Job done.