Database In-Memory Base Level Feature Now Available in Oracle 19c (19.8.0.0.0) RU
The Database In-Memory Base Level feature is now available for Oracle Database 19c! Support
for the Base Level feature was added to 19c in the just released 19.8 RU.
As a brief review, with the Base Level feature you can use up to a 16GB column store
without having to license the Database In-Memory option.
This now gives anyone running Oracle Database 19c Enterprise Edition the ability to try
out Database In-Memory for in-production workloads, without having to license the
full Database In-Memory option first.
Simply apply the 19.8 RU and set the INMEMORY_FORCE initialization parameter to BASE_LEVEL and
set the INMEMORY_SIZE parameter to a value up to 16GB. When you restart your database,
the Base Level feature will then be enabled.
That's really all there is to it.
Oracle Database In-Memory will now have a "Base Level" feature.
This will allow the use of Database In-Memory with up to a 16GB column store for no extra cost.
That's not a typo. Database In-Memory will be free to use with up to a 16GB column store!
Note: Apply July 2020 RU to get Oracle 19c (19.8.0.0.0)
Required Softwares:
-------------------
Latest OPatch: p6880880_190000_Linux-x86-64.zip
GI Release Update (July 2020): p31305339_190000_Linux-x86-64.zip
Database Release Update (July 2020): p31281355_190000_Linux-x86-64.zip
Step-1: Check the cluster instances
------------------------------------
[oracle@rac1-oel72 ~]$ ps -ef | grep pmon
oracle 6818 1 0 20:35 ? 00:00:00 asm_pmon_+ASM1
oracle 13729 1 0 20:38 ? 00:00:00 ora_pmon_orcl1
oracle 21836 12071 0 20:46 pts/0 00:00:00 grep --color=auto pmon
[oracle@rac1-oel72 ~]$
[oracle@rac2-oel72 ~]$ ps -ef | grep pmon
oracle 2127 1 0 20:43 ? 00:00:00 asm_pmon_+ASM2
oracle 2692 1 0 20:44 ? 00:00:00 ora_pmon_orcl2
oracle 3033 1 0 20:45 ? 00:00:00 mdb_pmon_-MGMTDB
oracle 3455 20208 0 20:46 pts/0 00:00:00 grep --color=auto pmon
[oracle@rac2-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1-oel72
Instance orcl2 is running on node rac2-oel72
[oracle@rac1-oel72 ~]$
Step-2: Check the patches
--------------------------
[oracle@rac1-oel72 ~]$ . oraenv
ORACLE_SID = [orcl1] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ sh /u01/app/oracle/product/19.3.0/dbhome_1/OPatch/opatch lspatches
31281355;Database Release Update : 19.8.0.0.200714 (31281355)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ ssh rac2-oel72
Last login: Sun Jul 19 21:08:10 2020
[oracle@rac2-oel72 ~]$
[oracle@rac2-oel72 ~]$ . oraenv
ORACLE_SID = [orcl2] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2-oel72 ~]$
[oracle@rac2-oel72 ~]$ sh /u01/app/oracle/product/19.3.0/dbhome_1/OPatch/opatch lspatches
31281355;Database Release Update : 19.8.0.0.200714 (31281355)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@rac2-oel72 ~]$
Step-3: Increase the SGA ans restart the cluster instances
----------------------------------------------------------
[oracle@rac1-oel72 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 19 20:14:00 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 5952M
sga_min_size big integer 0
sga_target big integer 5952M
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> show parameter inmemory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled boolean TRUE
inmemory_automatic_level string OFF
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_optimized_arithmetic string DISABLE
inmemory_prefer_xmem_memcompress string
inmemory_prefer_xmem_priority string
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
inmemory_xmem_size big integer 0
optimizer_inmemory_aware boolean TRUE
SQL>
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl1
SQL> create pfile='/home/oracle/initorcl_07192020.ora' from spfile;
File created.
SQL> alter system set sga_target=20480M scope=spfile;
System altered.
SQL> alter system set sga_max_size=20480M scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1-oel72
Instance orcl2 is running on node rac2-oel72
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl stop database -d orcl
[oracle@rac1-oel72 ~]$ srvctl start database -d orcl
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-27104: system-defined limits for shared memory was misconfigured
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/rac2-oel72/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.orcl.db' on 'rac2-oel72' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-27104: system-defined limits for shared memory was misconfigured
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/rac1-oel72/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.orcl.db' on 'rac1-oel72' failed
[oracle@rac1-oel72 ~]$ cat /etc/sysctl.conf | grep kernel | grep shm | grep -v "#"
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
[oracle@rac1-oel72 ~]$
Note: Set the 'Kernel' parameters and SGA values according to the InMemory settings
Step-4: Change the values 'inmemory_force' and 'inmemory_size'
-------------------------------------------------------------
[oracle@rac1-oel72 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1-oel72
Instance orcl2 is running on node rac2-oel72
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ . oraenv
ORACLE_SID = [orcl1] ? orcl1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ sqlplus sys@orcl as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 19 20:49:13 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 20G
sga_min_size big integer 0
sga_target big integer 20G
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> show parameter inmemory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled boolean TRUE
inmemory_automatic_level string OFF
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_optimized_arithmetic string DISABLE
inmemory_prefer_xmem_memcompress string
inmemory_prefer_xmem_priority string
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
inmemory_xmem_size big integer 0
optimizer_inmemory_aware boolean TRUE
SQL>
SQL> show sga
Total System Global Area 2.1475E+10 bytes
Fixed Size 9146112 bytes
Variable Size 1879048192 bytes
Database Buffers 1.9529E+10 bytes
Redo Buffers 57962496 bytes
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> alter system set inmemory_force = 'BASE_LEVEL' scope=spfile;
System altered.
SQL> alter system set inmemory_size = 16G scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl stop database -d orcl
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl start database -d orcl
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1-oel72
Instance orcl2 is running on node rac2-oel72
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ . oraenv
ORACLE_SID = [orcl1] ? orcl1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1-oel72 ~]$
[oracle@rac1-oel72 ~]$ sqlplus sys@orcl as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 19 20:56:08 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 20G
sga_min_size big integer 0
sga_target big integer 20G
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> show parameter inmemory_force
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_force string BASE_LEVEL
SQL>
SQL> show parameter inmemory_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 16G
SQL>
SQL> select instance_name, instance_number from gv$instance order by instance_number;
INSTANCE_NAME INSTANCE_NUMBER
---------------- ---------------
orcl1 1
orcl2 2
SQL>
Please refer for more details:
https://blogs.oracle.com/in-memory/base-level-198-ru
https://blogs.oracle.com/in-memory/oracle-database-in-memory-base-level-feature
Sunday, July 19, 2020
Database In-Memory Base Level Feature Now Available in Oracle 19c (19.8.0.0.0) Release Update (RU)
Subscribe to:
Post Comments (Atom)
Hi,
ReplyDeleteThis is failing due to the following error. Upgraded db to 19.8 RU, OS is rhel 7.2
ORA-12754: Feature 'In-Memory Base Level' is disabled due to missing capability 'Runtime Environment'.
[oracle@mnipdhcdb3 dbs]$ cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
SQL>select * from product_component_version;
PRODUCT VERSION VERSION_FULL STATUS
--------------------------------------------- -------------------- -------------------- -----------------------------------
Oracle Database 19c Enterprise Edition 19.0.0.0.0 19.8.0.0.0 Production
SQL> alter system set inmemory_force = 'BASE_LEVEL' scope=spfile;
System altered.
SQL> alter system set inmemory_size = 16G scope=spfile;
System altered.
SQL> startup
ORA-12754: Feature 'In-Memory Base Level' is disabled due to missing capability 'Runtime Environment'.