This article guides you to perform the installation of Oracle Linux 7 (OEL7).
Choose “Install Oracle Linux 7.3” and press enter.
Step:1 Select the type of configuration do you want, typical or custom. Mostly in this below options typical is recommended if you need to set some advanced configuration you can choose custom option.
Step:2 Browse the iso file location and select I will install the operating system later option.
Step:3 Select a guest operating system, I have chosen the Linux operating system.
Step:4 Specify the virtual machine name and location of the virtual machine.
Step:5 Configure the maximum disk size I have configured the 200GB disk size and select the option store virtual disk as a single file.
Step:6 Specify the memory size of the virtual machine.
Step:7 Use ISO image file, specify the OS location OEL 7(Linux 7) software.
Step:8 Set the Date and time, choose the timezone, region and city as Asia and Kolkata respectively.
Step:9Select the base environment and adds on for selected environment, select all the check boxes for all the base environments.
Step:10 Specify the installation destination, here we can partition our storage either by automatic partitioning or customized partitioning.
Step:11 Do the partition manually. Such as create the following mount points /boot , / , swap.
Step:12 This page asks for the confirmation, whether we can go with the configured partitions.
Step:13 Setup the password for the root user.
Step:14 We can create the user, I have created the user oracle and set up the password for the oracle user. Now we all set for the installation we can proceed further.
Step:15 Now we can login to the oracle user and get in to the VM.
Step:16 Choose the language as English(United states).
Step:17 Now we can use the Virtual Machine installed with Linux 7 OS and I checked my internet is working within the VM by pinging google.com.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
set lines 750 pages 9999
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;
Users Details Session:
For Cluster:
set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr
and a.inst_id = b.inst_id and type='USER' order by logon_time;
For Standalone:
set pages 500
set linesize 750
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr
and type='USER' order by b.sid;
(or)
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To Find Session Information Details based on SID or SPID or CLIENTPID :
col program for a15
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
Timing details, Client PID of associated oracle SID:
undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);
Checking Timing Details of SID and waiting event:
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;
Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);
Checking what is the Last SQL
undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);
All Active and Inactive connections
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action,
s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';
Active sessions
select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);
Session details from Session long ops
select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;
To list the nodes
set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /+ CHOOSE/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB ..............: '||SUPPORT_DB
from apps.fnd_nodes;
Session details through SPID:
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '18924');
To list count of connections from other machines:
select count(1),machine from gv$session where inst_id=3 group by machine;
To get total count of sessions and processes:
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext through sqladdress:
select sql_address from v$session where sid=8794;
select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;
To find sqltext for different sql hashvalue:
select hash_value,sql_text from v$sql where hash_value in (193756789,156412346,
456812142,8971248686,2358702945);
To list long running forms user sessions:
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username:
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE'; SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs:
select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');
To find Sql Text given SQLHASH & SQLADDR:
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece; select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
To find Undo Generated For a given session:
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sessionid';
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
col sid_serial for a20
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50SELECT a.inst_id,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
,(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
-- AND c.hash_value = a.sql_hash_value
AND a.inst_id=b.inst_id
ORDER BY 6 desc; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50
break on report
compute SUM of MB_USED on report
SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, 5 desc; (or) select ROUND(SUM(tempseg_size)/1048576) temp_mb from gv$sql_workarea_active WHERE sid=&sid;
History of Temp tablespace usage:
select sql_id,SQL_PLAN_HASH_VALUE,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-10 and TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) group by sql_id,SQL_PLAN_HASH_VALUE order by sql_id;
Shrink Temp file:
ALTER TABLESPACE temp SHRINK TEMPFILE '+ORA_DATA/JAPAN/tempfile/temp.313.868020865' KEEP 500M; Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
select inst_id,name,value from gv$parameter where name like '%undo%';
UNDO tablespaces Free As per expired segments:
SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');
does undo have expired segments?
select status, count(1) from dba_undo_extents group by status;
User Generated UNDO
col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id
and s.username = '<user>'
/
UNDO tablespace usage by USER
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s
where t.addr=s.taddr and s.sid='&sid';
Recreating the undo tablespace or resolving to undo issue
select a.name,b.status , d.username , d.sid , d.serial#
from v$rollname a,v$rollstat b, v$transaction c , v$session d
where a.name IN ( select segment_name
from dba_segments where tablespace_name = 'UNDOTBS1')
and a.usn = b.usn
and a.usn = c.xidusn
and c.ses_addr = d.saddr;
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;
If TRUE means it will not show any undo errors, So some times job may complete successfully.
Find active, expired, unexpired undo
select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' );select count(status) from dba_undo_extents where status = 'EXPIRED';
select count(status) from dba_undo_extents where status = 'UNEXPIRED';
select count(status) from dba_undo_extents where status = 'ACTIVE';
Find active, expired, and unexpired undo by Size in MB
select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME;select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;
Undo advisor:
SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a,
v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec
FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us:
NID utility has been introduced with Oracle 10g to change the database name and id. Without NID changing the DBNAME is only possible by recreating the control files. Changing the DBID was not possible before, as this is the database unique identifier. Giving a database a new DBNAME is common after migration of a database instance using Data Guard or duplicate where DBNAME and DB_UNIQUE_NAME differs and shall be synchronized afterwards. In some situation an ORA-01103 error occurs and changing the DBNAME to a new value may be needed.
OVERVIEW:
Verifying the pluggable database
For rename the pluggable database, We need to open the database in Restricted Mode
Set the container database name
Renaming the pluggable database
Open the pluggable database
Note :For an example , I am taking Source PDB “Mumbai” and renaming as “Noida”,
Step 1:
Verifying the available pluggable database in our local CDB
SQL> select con_id,name,open_mode from v$Pdbs;
Step 2:
Before we need to rename the pluggable database ,We should bring the PDB close and open it in “RESTRICTED MODE”
alter pluggable database mumbai close;
Step 3:
Opening the PDB in restricted mode,
alter pluggable database mumbai open restricted;
Step 4:
Set the PDB “MUMBAI”
alter session set container=mumbai;
Step 5:
Here we are renaming the pluggable database name “mumbai” as “noida”
alter pluggable database rename global_name to noida;
Step 6:
Here we can check the renamed pdb by issuing the below command
select con_id,name,open_mode from v$Pdbs;
But , the database is in restricted mode so we need to open the database,
The following major features are new in this release.
Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.
Changes in Oracle Database Release 19c, Version 19.1
New Features
Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
Parallel query is supported.
A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.
Reference : docs.oracle.com Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
The following major features are new in this release.
Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.
Changes in Oracle Database Release 19c, Version 19.1
New Features
Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
Parallel query is supported.
A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.
Reference : docs.oracle.com Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
order by
created;
SQL Baselines:
Check the current baseline:
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,sql_text from dba_sql_plan_baselines;select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_b7pnq6yp8da6a29d0d9b7'));
Drop the baseline:
set serveroutput on
DECLARE
i NATURAL;
BEGIN
i := dbms_spm.drop_sql_plan_baseline('SQL_b3d69637aa86a8ca');
dbms_output.put_line(i);
END;
/
SQL_HANDLE PLAN_NAME ENA ——————————————————————————————————————————– ——————————————————————————————————————————– — SQL_d3318f33dfac7bc2 SQL_PLAN_d6ccg6ggusyy2ee7f0c12 YES variable sqlid number;
exec :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE=> 'SQL_d3318f33dfac7bc2', PLAN_NAME => 'SQL_PLAN_d6ccg6ggusyy2ee7f0c12', ATTRIBUTE_NAME=> 'enabled', ATTRIBUTE_VALUE => 'NO'); Now fix the plan of 31pux6bymf1d4 to 5qbbnv0abm2vx 5qbbnv0abm2vx variable sqlid number; execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’5qbbnv0abm2vx’, PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => ‘SQL_d3318f33dfac7bc2’); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5qbbnv0abm2vx',&childnumber,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +OUTLINE +PREDICATE +COST +BYTES')); 4197102931
Extract hint from all profile/baseline/patch :
All in one script:
set pagesize 60
set linesize 180
set trimspool on
column plan_name format a32
column signature format 999,999,999,999,999,999,999
column category format a10
column hint format a70 wrap word
break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1
spool sql_profile_baseline_11g
select prf.plan_name,prf.signature,decode(obj_type,1,'Profile',2,'Baseline',3,'Patch',
'Other') opt_type,prf.category,prf.plan_id,extractvalue(value(hnt),'.') hint from
( select /*+ no_eliminate_oby */ so.name plan_name, so.signature,so.category,
so.obj_type,so.plan_id,sod.comp_data from
sqlobj$ so,sqlobj$data sod where so.name = '&m_plan_name'
and sod.signature = so.signature and sod.category = so.category
and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id
order by signature, obj_type, plan_id ) prf, table ( select
xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint')
)from dual ) hnt;
Taking SQL HINTS from memory:
SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
from
xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from v$sql_plan where
sql_id like nvl('&sql_id',sql_id)
and child_number = &child_no
and other_xml is not null ) ) d;
Taking SQL HINTS from AWR:
SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
from xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from
-- v$sql_plan
dba_hist_sql_plan where sql_id like nvl('&sql_id',sql_id) and other_xml is not null )) d;
Taking SQL HINTS from PROFILES
select hint as outline_hints
from (select p.name, p.signature, p.category, row_number()
over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, dba_sql_profiles p,
table(xmlsequence(extract(xmltype(sd.comp_data),
'/outline_data/hint'))) t
where sd.obj_type = 1
and p.signature = sd.signature
and p.category = sd.category
and p.name like ('&profile_name'))
order by row_num;
Taking SQL HINTS from PLAN_TABLE:
SELECT regexp_replace(extractvalue(value(d), '/hint'),'''','''''') plan_hint
from xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from plan_table where other_xml is not null
and plan_id = (select max(plan_id) from plan_table)
and rownum=1 ) ) d;
Fixing the PLAN HASH VALUE:
Resolution normally is to fix the execution plan in 11g by running
variable x number
begin
:x :=
dbms_spm.load_plans_from_cursor_cache(
sql_id=>'&sql_id',
plan_hash_value=>&plan_hash,
fixed=>'YES');
end;
/
To see the hints from sql_profile
select hint from (
select s.sql_id, sd.obj_type,
row_number() over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, v$sql s,
table(xmlsequence(extract(xmltype(sd.comp_data), '/outline_data/hint'))) t
where sd.obj_type = 1
and s.exact_matching_signature = sd.signature
and s.sql_id = '&sql_id')
order by row_num
/
Purge the old plan hash value from memory:
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/
All in one script
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
