All about SGA

All about SGA

SYS @ catdb > select value/1024/1024 mb from v$parameter where upper(name) = 'SGA_MAX_SIZE';

        MB
----------
       800

SYS @ catdb > select pool, round(sum(BYTES)/1024/1024) MB from V$SGASTAT group by pool;

POOL                 MB
------------ ----------
                    292
java pool             4
shared pool         208
large pool           12

SYS @ catdb > select name , value/1024/1024 MB
  from v$parameter
  where upper(name) in (
  'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
  DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');  2    3    4    5

NAME                                                                                     MB
-------------------------------------------------------------------------------- ----------
db_cache_size                                                                             0
db_keep_cache_size                                                                        0
db_recycle_cache_size                                                                     0

SYS @ catdb > SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0;  2    3

COMPONENT                                                                MB
---------------------------------------------------------------- ----------
shared pool                                                             208
large pool                                                               12
java pool                                                                 4
DEFAULT buffer cache                                                    284

SYS @ catdb > SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;  2    3

COMPONENT                                                        CURRENT_SIZE        MIN        MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                               208        132        208
large pool                                                                 12         12         72
java pool                                                                   4          4          4
streams pool                                                                0          0          0
SGA Target                                                                520        520        520
DEFAULT buffer cache                                                      284        284        356
KEEP buffer cache                                                           0          0          0
RECYCLE buffer cache                                                        0          0          0
DEFAULT 2K buffer cache                                                     0          0          0
DEFAULT 4K buffer cache                                                     0          0          0
DEFAULT 8K buffer cache                                                     0          0          0
DEFAULT 16K buffer cache                                                    0          0          0
DEFAULT 32K buffer cache                                                    0          0          0
Shared IO Pool                                                              0          0          0
PGA Target                                                                280        280        280
ASM Buffer Cache                                                            0          0          0

16 rows selected.

SYS @ catdb > select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;  2    3    4    5

START_TIM END_TIME  STATUS    COMPONENT                                                        OPER_TYPE     OPER_MODE PARAMETER                    INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- --------------- -------------- -------------
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 8K buffer cache                                          STATIC                  db_8k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 4K buffer cache                                          STATIC                  db_4k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  RECYCLE buffer cache                                             STATIC                  db_recycle_cache_size              0       0             0
24-JUN-20 24-JUN-20 COMPLETE  KEEP buffer cache                                                STATIC                  db_keep_cache_size                 0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 2K buffer cache                                          STATIC                  db_2k_cache_size                   0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             STATIC                  db_cache_size                      0     300           300
24-JUN-20 24-JUN-20 COMPLETE  ASM Buffer Cache                                                 STATIC                  db_cache_size                      0       0             0
24-JUN-20 24-JUN-20 COMPLETE  PGA Target                                                       STATIC                  pga_aggregate_target               0     280           280
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 16K buffer cache                                         STATIC                  db_16k_cache_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT 32K buffer cache                                         STATIC                  db_32k_cache_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  SGA Target                                                       STATIC                  sga_target                         0     520           520
24-JUN-20 24-JUN-20 COMPLETE  streams pool                                                     STATIC                  streams_pool_size                  0       0             0
24-JUN-20 24-JUN-20 COMPLETE  java pool                                                        STATIC                  java_pool_size                     0       4             4
24-JUN-20 24-JUN-20 COMPLETE  large pool                                                       STATIC                  large_pool_size                    0      72            72
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      STATIC                  shared_pool_size                   0     132           132
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             INITIALIZING            db_cache_size                    300     300           300
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          IMMEDIATE shared_pool_size                 132     136           136
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        IMMEDIATE db_cache_size                    300     296           296
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             GROW          DEFERRED  db_cache_size                    296     356           356
24-JUN-20 24-JUN-20 COMPLETE  large pool                                                       SHRINK        DEFERRED  large_pool_size                   72      12            12
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 136     140           140
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    356     352           352
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    352     348           348
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 140     144           144
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    348     344           344
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 144     148           148
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    344     340           340
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 148     152           152
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    340     336           336
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 152     156           156
24-JUN-20 24-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 156     160           160
24-JUN-20 24-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    336     332           332
26-JUN-20 26-JUN-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 160     168           168
26-JUN-20 26-JUN-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    332     324           324
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    324     316           316
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 168     176           176
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    316     308           308
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 176     184           184
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 184     192           192
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    308     300           300
02-JUL-20 02-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 192     200           200
02-JUL-20 02-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    300     292           292
03-JUL-20 03-JUL-20 COMPLETE  DEFAULT buffer cache                                             SHRINK        DEFERRED  db_cache_size                    292     284           284
03-JUL-20 03-JUL-20 COMPLETE  shared pool                                                      GROW          DEFERRED  shared_pool_size                 200     208           208

44 rows selected.

How To Open The Standby Database When The Primary Is Lost in oracle11g ?

How To Open The Standby Database When The Primary Is Lost in oracle11g ?
July 02, 2020


Scenario:- So this scenario shows how to open your standby database in read/write mode when you dont have any access(Lost) on primary database.
Here i'm trying to make a test case to demonstrate the following scenario.Currenly both primary and standby database are in sync.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

At primary database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26
  
  
At standby database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26

So at this situation we totally power off the primary database server to test the scenario.

At primary:-
SYS @ catdb >shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ catdb >exit

[root@server1 ~]# poweroff

Now at standby database:-
Open the database in mount stage.
SYS @ catdb >shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL:> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


Finish the Recovery process in standby database:-
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Once done, now activate the standby database:-
SYS @ catdb >ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the status:-
SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY


Now open the database in read/write mode.
SYS @ catdb >ALTER DATABASE OPEN;

Database altered.

SYS @ catdb >select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SYS @ catdb >SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


So, finally the previous standby database is now a new primary database with read/write mode in open stage.

Script to delete multiple tables using a single query

Script to delete multiple tables using a single query
July 03, 2020

SYS @ catdb > create table xyz (id number);

Table created.

SYS @ catdb > create table t1 as select * from xyz;

Table created.


SYS @ catdb > SELECT * FROM TAB where TNAME IN ('T1','XYZ');

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
XYZ                            TABLE


SYS @ catdb >
SYS @ catdb > BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'DROP TABLE ' || i.table_name;

  END LOOP;

END;
/  

PL/SQL procedure successfully completed.

SYS @ catdb > SELECT * FROM TAB where TNAME IN ('T1','XYZ');

no rows selected

Monitoring Temporary tablespace usage using shell script

Monitoring Temporary tablespace usage using shell script

The following script will report if temporary tablespace  hits 80% of its usage.

From oracle user:
[oracle@server1 ~]$ vi /home/oracle/check_temp_tbs.sql

select a.tablespace_name tablespace,
         d.TEMP_TOTAL_MB,
         sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
         d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
        (
          select   b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
          from     v$tablespace b, v$tempfile c
          where    b.ts#= c.ts#
          group by b.name, c.block_size
        ) d
where    a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;

Now lets create the shell-script which will monitor the space usage of temp tablespaces:-
From oracle user

[oracle@server1 ~]$ vi /home/oracle/alert_temp.sh
#!/bin/bash
export ORACLE_SID=catdb
sqlplus -S / as sysdba @/home/oracle/check_temp_tbs.sql > /home/oracle/temptbs.txt
space=`cat /home/oracle/temptbs.txt`
if [ $space -gt 80 ]; then
mail -s echo "Attention!! Low space in  Temporary Tablespaces in $ORACLE_SID instance!" shashidba1208@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert_temp.sh > /dev/null

NOTE:- Please give proper permission to check_temp_tbs.sql and alert_temp.sh if require.
P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.

What is Standby snapshot Database?

What is Standby snapshot Database?

Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database

receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back

into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the

snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a

physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users

to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted

into a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby

database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions

which were made in standby database while it was open in READ WRITE mode will be flushed out.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled.

How to drop undo tablespace in oracle 11g?

How to drop undo tablespace in oracle 11g?

SYS @ catdb > set pagesize 999
SYS @ catdb > set linesize 300
SYS @ catdb > /

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/users01.dbf                    USERS
/u01/app/oracle/oradata/catdb/undotbs01.dbf                  UNDOTBS1
/u01/app/oracle/oradata/catdb/sysaux01.dbf                   SYSAUX
/u01/app/oracle/oradata/catdb/system01.dbf                   SYSTEM
/u01/app/oracle/oradata/catdb/example01.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/example04.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/example02.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/tb_name01.dbf                  TB_NAME
/u01/app/oracle/oradata/catdb/testdb.dbf                     TESTDB
/u01/app/oracle/oradata/catdb/undotbs02.dbf                  UNDOTBS1
/u01/app/oracle/oradata/catdb/system02.dbf                   SYSTEM

11 rows selected.

First create tablespace and then we will see how to drop..
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME='UNDOTBS1';

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs01.dbf                  UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs02.dbf                  UNDOTBS1

SYS @ catdb > drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.

SYS @ catdb > create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs02.dbf' size 10M reuse autoextend on maxsize 50M;
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs02.dbf' size 10M reuse autoextend on maxsize 50M
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/catdb/undotbs02.dbf' - file already part of database

can not create as we have already datafile of this name.
SYS @ catdb > create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs03.dbf' size 10M reuse autoextend on maxsize 50M;

Tablespace created.

SYS @ catdb > alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SYS @ catdb > drop tablespace UNDOTBS1 including contents;^C

SYS @ catdb >
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME='UNDO%';

no rows selected

SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME like 'UNDO%';

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs01.dbf                  UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs02.dbf                  UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs03.dbf                  UNDOTBS2

SYS @ catdb > drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME like 'UNDO%';

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs03.dbf                  UNDOTBS2

SYS @ catdb > select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                    TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/users01.dbf                    USERS
/u01/app/oracle/oradata/catdb/sysaux01.dbf                   SYSAUX
/u01/app/oracle/oradata/catdb/system01.dbf                   SYSTEM
/u01/app/oracle/oradata/catdb/example01.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/example04.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/example02.dbf                  EXAMPLE
/u01/app/oracle/oradata/catdb/tb_name01.dbf                  TB_NAME
/u01/app/oracle/oradata/catdb/testdb.dbf                     TESTDB
/u01/app/oracle/oradata/catdb/system02.dbf                   SYSTEM
/u01/app/oracle/oradata/catdb/shashi.dbf                     SHASHI
/u01/app/oracle/oradata/catdb/undotbs03.dbf                  UNDOTBS2

11 rows selected.

SYS @ catdb >

Oracle 18c: New features of Oracle Database 18c..

18c new features which are important in our opinion.

(A) Availability

1. Oracle Data Guard Multi-Instance Redo Apply Supports Use of Block Change Tracking Files for RMAN Backups

Multiple-Instance Redo Apply (also known as MIRA) now supports BCT (Block Change Tracking) backups. This is an effective incremental backup method large database, and both master and slave are RAC, and the backup happens on the slave this kind of DB environment.

2. Automatic Correction of Non-logged Blocks at a Data Guard Standby Database

added two new standby logging modes (mainly to speed up the primary database loading data):

One is Standby Nologging for Data Availability, where the commit of a loading operation is delayed until all standby have applied data.

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;

One is Standby Nologging for Load Performance. This mode is similar to the previous one, but it will not send data when it encounters the network bottleneck during loading data. This guarantees the loading performance but may lose the data which can be obtained again from primary.

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

3. Shadow Lost Write Protection

Create a shadow tablespaces to provide data protection. With this feature you may not need ADG to provide additional lost write protection.

Do you notice that this feature like double write of the MySQL?

4. Backups from non-CDBs are usable after migration to CDB

The original non-CDB can be migrated as a PDB to the existing CDB with this new function.

5. Support for PDBs as Shards and Catalogs

finally, oracle shard could be a pdb.

However, it only supports a single pdb in a single cdb. What’s the point?

6. User-Defined Sharding Method

This feature once appeared in the beta version of 12.2 but canceled in the official release. Now it got released again.

7. Consistency Levels for Multi-Shard Queries

Provides the MULTISHARD_QUERY_DATA_CONSISTENCY initialization parameter, to avoid SCN synchronization during slicing across slices you can be set it before the execution

8. Manual termination of run-away queries

Now you can manually kill a statement without breaking the session:

ALTER SYSTEM CANCEL SQL.

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL, @INST_ID, SQL_ID’;

(B) Big Data and Data Warehousing

9. Approximate Top-N Query Processing

Note: In 18c, APPROX_COUNT and APPROX_SUM were added to make full use of theAPPROX_RANK.

10. LOB support with IMC, Big Data SQL

the memory database start support LOB objects.

(C) Database Overall

11. Copying a PDB in an Oracle Data Guard Environment

Two new parameters have been added to facilitate the creation of PDBs in an ADG environment.

➢ One is STANDBY_PDB_SOURCE_FILE_DIRECTORY, which automatically finds the ADG data file path. (Note that before 18c, if you inserted a pdb into a standby environment, cdbwould need to manually copy the file to pdb’s OMF path)

➢ The other is STANDBY_PDB_SOURCE_FILE_DBLINK, which is used to automatically find the ADG file path when remote clone is used (Note: Before 18c, if you are a local clone, you do not need to copy the data file, but the remote clone requires manual copying).

 

12. PDB Lockdown Profile Enhancements

PDB lockdown profiles can now be created in application root and CDB root.You can now create another pdb lockdown profile based on a pdblockdown profile.18c contains three default lockdown profiles: PRIVATE_DBAAS, SAAS, PUBLIC_DBAAS

13. Refreshable PDB Switchover

PDB refresh has always been known as the ADG for the poor, this feature is now getting much better in 18c. Switchover is supported. The switchover is divided into two scenarios outside the planned kernel plan.

Planed switchover: it can be cut back and used mainly to balance the load of the CDB.

Unplanned switchover: mainly for the PDB master failure, do not switch the entire CDB.

14. PDB Snapshot Carousel

The pdb snapshot backup carousel, reserves 8 copies and is backed up every 24 hours by default.

ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS;

15. New Default Location of Oracle Database Password File

Note that the new password file path is already in ORACLE_BASE, not ORACLE_HOME.

16. Read-Only Oracle Home

You can use dbca or roohctl -enable to install read only oracle home.Run the orabasehomecommand to check if the current Oracle Home is read-only. If the output of this command is the same as $ORACLE_HOME, Oracle Home is read-write. If the output is ORACLE_BASE/homes/HOME_NAME, Oracle Home is read-only.

17. Online Merging of Partitions and Subpartitions

Supports online consolidation of partitions and the ONLINE keyword is required.

18. Concurrent SQL Execution with SQL Performance Analyzer

The SPA can run in parallel (by default, it is serial), helping you to complete the SPA test faster.

(D) Performance

19. Automatic In-Memory

this feature evicts the infrequently accessed IM columns out of memory in the situation of memory insufficiency, based on the Heat Map.

20. Database In-Memory Support for External Tables

External tables support IM features.

21. Memoptimized Rowstore

There is a memoptimize pool area in the SGA. The size is set by the MEMOPTIMIZE_POOL_SIZE parameter. When fast lookup is turned on, the memory area can be used for quick search.

Open fast lookup, need to add keywords in the table statement:

fast lookup would be used based on the primary key query

Memoptimized Rowstore will greatly improve the high-frequency query based on the primary key in the Internet of Things.

(E) RAC and Grid

22. ASM Database Cloning

You can clone pdb based on ASM. Use the flex diskgroup to achieve.

23. Converting Normal or High Redundancy Disk Groups to Flex Disk Groups without Restricted Mount

We think use the Flex disk groups is great.

(f) Security

24. Integration of Active Directory Services with Oracle Database

Combined with Microsoft’s AD.

Before 18c, interaction with Oracle Enterprise User Security (EUS) was required. Now, you can use the centrally managed users (CMU) to mappiing the AD users and groups directly with the Oracle users and roles.

(G) Others

25. Add initialization parameters:

ADG_ACCOUNT_INFO_TRACKING

FORWARD_LISTENER

INMEMORY_AUTOMATIC_LEVEL

INMEMORY_OPTIMIZED_ARITHMETIC

MEMOPTIMIZE_POOL_SIZE

MULTISHARD_QUERY_DATA_CONSISTENCY

OPTIMIZER_IGNORE_HINTS

OPTIMIZER_IGNORE_PARALLEL_HINTS

PARALLEL_MIN_DEGREE

PRIVATE_TEMP_TABLE_PREFIX

STANDBY_PDB_SOURCE_FILE_DBLINK

STANDBY_PDB_SOURCE_FILE_DIRECTORY

TDE_CONFIGURATION

UNIFIED_AUDIT_SYSTEMLOG

WALLET_ROOT

It’s worth mentioning OPTIMIZER_IGNORE_PARALLEL_HINTS. In a pure OLTP system, you can finally disable developers’ uncontrolled concurrency (often not writing concurrency). 

26. dbms_session.sleep

Exec dbms_session.sleep(3);

Finally, it is no longer necessary to grant grants for dbms_lock separately.

Until here the article. Thanks for Reading!