Loading...

Follow Blog dbi services » Oracle on Feedspot

Continue with Google
Continue with Facebook
or

Valid

This year again the APEX connect conference spans over three days with mixed topics around APEX, like JavaScript, PL/SQL and much more.
After the welcome speech and the very funny and interesting Keynote about “PL/SQL: A Career Built On Top Of PL/SQL – 25 years of Coding, Sharing, and Learning” by Martin Widlake, I decided to attend presentations on following topics:
– Oracle Text – A Powerful Tool for Oracle APEX Developers
– Make It Pretty! MIP your APEX application!
– REST Services without APEX – but with APEX_EXEC
– Microservices with APEX
– SQL Magic!
– The UX of forms

PL/SQL: A Career Built On Top Of PL/SQL – 25 years of Coding, Sharing, and Learning:

Martin Widlake shared the story of 25 years development on Oracle from version 6 to the newest 19c.
The most important to retain from his professional journey is that “Good developers are made by other developers” and “Everything you learn will have some return sometime in the future”. That means sharing is the key, keep yourself curious and never stop learning, even things that are not yet obviously useful.

Oracle Text – A Powerful Tool for Oracle APEX Developers

That feature is embedded as a standard in Oracle databases since 1997 when it was named Car Text. In 1999 it became Intermedia Text and finally Oracle Text in 2001. It allows to index text based fields of the database as well as files in BLOBs, allowing much faster and easier search of text patterns (words, sentences, …). We went thru aspects like syntax, fuzzy search, snippets and lexer.
As search combinations require usage of specific operators and delimiters, which are cumbersome for end users, there is a useful package written by Roger Ford that allows to convert simple “Google” like requests into the right format for Oracle Text: PARSER download
His blog will provide nice information about it and Oracle documentation provides all details to the usage of Oracle Text.
You can find further information on following Blog:
Oracle text at a glance

Make It Pretty! MIP your APEX application!

The business logic is the heart of the application but the UI is its face and what users will judge first.
There are some rules which to Make It Pretty (MIP).
First of all it needs to fullfil user needs by either:
– stick to company brand rules
– stick to company webpage design
– stick to user wishes (can be drafted with template monster
Technical and non-technical aspects need to be considered.
Following Design rules help to improve the UI:
– Be consistent
– Make it intuitive
– Be responsive (give feedback to users)
– Keep it simple (not crowded)
– Fonts: max 2 per page, 16px rule (verify on fontpair.co)
– Color rules (verify on contrast checker)
– Have imagination
– Know your APEX universal theme

REST Services without APEX – but with APEX_EXEC

APEX is based on metadata to store definitions and PL/SQL packages support the engine.
That means APEX metadata support can be used outside the APEX application in any PL/SQL code.
One particular APEX PL/SQL package is APEX_EXEC introduced in APEX 18.1. It allows to abstract the data format (XML, json, …) in websources in order to be able to use data as it would come from any local table. It also takes care of pagination from web services to make the data retrieval transparent. But in order to make use of that package an APEX session must first be created to initiate the needed metadata. Fortunately this is made easy since APEX 18.1 with procedure create_session from the apex_session package.
The next version of APEX 19.2 might integrate websource modules with interactive grid.

Microservices with APEX

APEX can be compared to microservices by looking at the characteristics:
– Scalability
– Fail-safe
– Maintainable
– Technology independent
– Database independent
– Small
And it mostly matches!
But APEX also overrules the microservices drawbacks:
– Complex architecture
– Complex testing
– Migration efforts
– Complex development
To have a behavior close to microservices, APEX applications have to make use of web services for the data management and the interfacing with any kind of other services. This allows to clearly separate data management and applications. ORDS allows to enable REST at schema and also object level within APEX. Caching also needs to be considered based data change frequency to lower the lag time of data handling.

SQL Magic!

Since Oracle 12c the database provides the json data guide which allows easy json data manipulation like any standard table data. This comes also with new views like user_json_data_guide.
Oracle 11g introduced Invisible columns that hides columns from table description as well as standard “select *” but not specific select statements. This can be used to deprecate columns or add new columns without breaking existing applications with “select *”. Even though “select *” should be avoided in applications.
Oracle 12c also introduced polymorphic table function that can be used with pipelined tables to create views allowing to pivot and transpose tables whatever number of columns and rows they have.
All those features are very useful and should be used further.

The UX of forms

User eXperience (UX) rules to be applied in forms go beyond APEX. The aim to:
– Reduce cognitive load
– Prevent errors
– Make it user friendly
The rules are following:
– One thing per page
– Put field labels above rather than side to
– Replace small dropdowns by radio buttons
– Use Interactive data lists for long dropdowns
– For login pages, be specific on username type (name, e-mail) and password definition rules
– Avoid * for required fields but rather flag Optional fileds
– Adapt field size on expected data length
– Use smart default values
– Use entry masks
– Use date picker
– Define check before you start pattern to guide users and reduce form length
All that will improve UX.

Cet article APEX Connect 2019 – Day 1 est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Up to Oracle 19c, there was no automatic way to backup the configuration of the broker. One solution was to manually copy all executed instructions in a file.
With Oracle 19c, there is now the possibility to export and to import the configuration of the broker. Indeed the new EXPORT CONFIGURATION command will enable to save the metadata contained in the broker configuration file to a text file. This can be very useful if I have to recreate my configuration.
In this blog I have tested this command with following configuration

DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    DB19C_SITE2 - Physical standby database
    DB19C_SITE3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 47 seconds ago)

DGMGRL>

We can see the syntax of the EXPORT command with the help command

DGMGRL> help export

Export Data Guard Broker configuration to a file.

Syntax:

  EXPORT CONFIGURATION [TO ];

DGMGRL>

Now let’s export the configuration

DGMGRL> EXPORT CONFIGURATION TO db19c_config.txt
Succeeded.
DGMGRL>

The file is generated in the trace files directory.

[oracle@primaserver trace]$ pwd
/u01/app/oracle/diag/rdbms/db19c_site1/DB19C/trace
[oracle@primaserver trace]$ ls -l db19c_config.txt
-rw-r--r--. 1 oracle oinstall 8469 May  4 12:25 db19c_config.txt
[oracle@primaserver trace]$

Let’s remove the configuration

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL> remove configuration;
Removed configuration

DGMGRL> show configuration
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>

Now let’s use the IMPORT command to rebuild the configuration

DGMGRL> IMPORT CONFIGURATION FROM db19c_config.txt
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL>

As we can see the configuration is disabled after the import

DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    DB19C_SITE2 - Physical standby database
    DB19C_SITE3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL>

So let’s enable it

DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    DB19C_SITE2 - Physical standby database
    DB19C_SITE3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 6 seconds ago)

DGMGRL>

The warning is due to the fact that the protection mode was set to MaxPerformance to be able to drop the configuration.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY ;
Succeeded.
DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    DB19C_SITE2 - Physical standby database
    DB19C_SITE3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 37 seconds ago)

DGMGRL>

Now let’s run the EXPORT command without specifying a file name

DGMGRL> EXPORT CONFIGURATION
Succeeded.
DGMGRL>

We can see in the trace directory that a default name is generated for the file

DB19C_dmon_5912_brkmeta_1.trc

If we run again the export command

DGMGRL> EXPORT CONFIGURATION
Succeeded.
DGMGRL>

A second default file is created

DB19C_dmon_5912_brkmeta_2.trc

Cet article Oracle 19C : Exporting and Importing Broker Configuration est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle 19C is now available on premise. There are lot of new features. One for the Data Guard Broker is that now we can dynamically change the fast-start failover target to a specified member in the list without disabling the fast-start failover.
I have tested this new feature and is describing this in this blog
I am using 3 servers with Oracle Linux
The Data Guard is already built and the broker is already configured
To enable the fast-start failover there are some requirements. Note that flashback database must be enabled for both databases.
First we put the the transport to SYNC for 3 databases

DGMGRL> edit database 'DB19C_SITE1' set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database 'DB19C_SITE2' set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database 'DB19C_SITE3' set property LogXptMode='SYNC';
Property "logxptmode" updated

After we change the protection to Maxavailability

DGMGRL>  EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

Then we set the fast-start failover target for both databases

DGMGRL> enable fast_start failover;
Enabled in Zero Data Loss Mode.

Below the status of the configuration. And we can see that DB19C_SITE2 is the target for the fast-start failover

DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    DB19C_SITE2 - (*) Physical standby database
    DB19C_SITE3 - Physical standby database

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

DGMGRL>

The status of the observer will also show as the active target

DGMGRL> show observer

Configuration - db19c

  Primary:            DB19C_SITE1
  Active Target:      DB19C_SITE2

Observer "standserver2" - Master

  Host Name:                    standserver2
  Last Ping to Primary:         2 seconds ago
  Last Ping to Target:          4 seconds ago

DGMGRL>

For example let’s say we want to switchover to DB19C_SITE3

DGMGRL> switchover to 'DB19C_SITE3';
Performing switchover NOW, please wait...
Error: ORA-16655: specified standby database not the current fast-start failover target standby

Failed.
Unable to switchover, primary database is still "DB19C_SITE1"
DGMGRL>

As we can see we cannot because the first fast-start failover target is DB19C_SITE2. We have to change it to DB19C_SITE3
To dynamiccaly do this change , we use the command SET FAST_START FAILOVER TARGET.

DGMGRL> SET FAST_START FAILOVER TARGET TO 'DB19C_SITE3';
Waiting for Fast-Start Failover target to change to "DB19C_SITE3"...
Succeeded.
DGMGRL>

We can query the broker to verify the change

DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE1 - Primary database
    DB19C_SITE3 - (*) Physical standby database
    DB19C_SITE2 - Physical standby database

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
SUCCESS   (status updated 22 seconds ago)

DGMGRL>

And then now I can switchover to DB19C_SITE3

DGMGRL> switchover to 'DB19C_SITE3';
Performing switchover NOW, please wait...
New primary database "DB19C_SITE3" is opening...
Operation requires start up of instance "DB19C" on database "DB19C_SITE1"
Starting instance "DB19C"...
Connected to an idle instance.
ORACLE instance started.
Connected to "DB19C_SITE1"
Database mounted.
Database opened.
Connected to "DB19C_SITE1"
Switchover succeeded, new primary is "DB19C_SITE3"
DGMGRL>

And the new status of the configuration

DGMGRL> show configuration

Configuration - db19c

  Protection Mode: MaxAvailability
  Members:
  DB19C_SITE3 - Primary database
    DB19C_SITE1 - (*) Physical standby database
    DB19C_SITE2 - Physical standby database

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL>

Cet article Oracle 19C : Dynamic Change of Fast-Start Failover Target est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle Grid Infrastucture can be installed on Windows platform. The steps are the same that on other platforms. In this blog we are going to install Oracle GI 18c on Windows 2016.I have two disks on my server
Disk 0 : for the system
Disk 1 : for the ASM
I am using a VirtualBox virtual machine.
We suppose that the grid infrastructure sofware is already downloaded and decompressed in the grid home.
Like on other platforms, we have to configure the ASM disk. In the documentation we can read :
The only partitions that OUI displays for Windows systems are logical drives that are on disks and have been marked (or stamped) with asmtoolg or by Oracle Automatic Storage Management (Oracle ASM) Filter Driver.
So Disk1 should not be formatted and should not be assigned to a letter.
Then the first step is to create logical partition using Windows diskpart utility.

Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>diskpart

Microsoft DiskPart version 10.0.14393.0

Copyright (C) 1999-2013 Microsoft Corporation.
On computer: RACWIN2

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           60 GB      0 B
  Disk 1    Online           20 GB    20 GB

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

DISKPART>

We can then list existing partition for Disk 1

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 0    Extended            19 GB  1024 KB
* Partition 1    Logical             19 GB  2048 KB

DISKPART>

Once the logical partition created we can launch the asmtool or asmtoolg utility. This utility comes with the grid software

c:\app\grid\18000\bin>asmtoolg.exe

The first time we executed the asmtoolg.exe command, we get following error

According to this Oracle support note Windows: asmtoolg: MSVCR120.dll is missing from your computer (Doc ID 2251869.1), we have to download and install Visual C++ 2013 Redistributable Package.
Once done we launch again the asmtoolg utility

Clicking on next, we can choose the disk we want to stamp for ASM

Click on Next

Click on Next

And Click to Finish. We can then list the disks marked for ASM with the asmtool utility.

C:\Users\Administrator>cd c:\app\18000\grid\bin

c:\app\18000\grid\bin>asmtool.exe -list
NTFS                             \Device\Harddisk0\Partition1              500M
NTFS                             \Device\Harddisk0\Partition2            60938M
ORCLDISKDATA0                    \Device\Harddisk1\Partition1            20477M
c:\app\18000\grid\bin>

Now it’s time to launch the gridSetup executable

c:\app\grid\18000>gridSetup.bat






We decide to ignore the Warning


At the end, we got an error from the cluster verification utility. But it is normal because we ignored some perquisites.


We can verify that the insallation was fine

c:\app\18000\grid>crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       racwin2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       racwin2                  STABLE
ora.asm
               ONLINE  ONLINE       racwin2                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      racwin2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       racwin2                  STABLE
ora.evmd
      1        ONLINE  ONLINE       racwin2                  STABLE
--------------------------------------------------------------------------------

c:\app\18000\grid>

We can connect to the ASM instance

C:\Users\Administrator>set oracle_sid=+ASM

C:\Users\Administrator>sqlplus / as sysasm

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Apr 27 05:49:38 2019
Version 18.3.0.0.0

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


SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED

SQL>

Conclusion
Once the grid infrastructure configured, we have to install the Oracle database software.

Cet article Oracle 18c Grid Infrastructure on Windows Server est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Below, I will try to explain, a particular case for the general error : ORA-01555 snapshot too old error..

Normally, when we have this error, we are trying to adapt the retention parameters or to tune our queries.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     3600 --extended from 900,
undo_tablespace                      string      UNDOTBS1

But, are some scenarios where the above rule is not working .

We got from the alert log file of the DB the sql id which caused the issue : pmrbk5fdfd665

But, when you want to search for it, in V$SQL/V$SQLAREA  is not there

SQL> select sql_fulltext from v$sql where sql_id like '%pmrbk5fdfd665%';

no rows selected

Why?

Seems that, sql_id is present in V$OPEN_CURSOR, with an input into the sqltext column.
The issue is coming from the fact that the statement is accessing a LOB column, which causes to Oracle to generate a new sql id.
The execution part related to the LOBs will not appear into V$SQL/V$SQLAREA and is not captured into the AWR reports.

SQL>  select distinct * from v$open_cursor
  2     where rownum < 25
  3     and sql_id like '%pmrbk5fdfd665%';

SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ -------- ----------- ---------------
0000000670A19780         74 my_user                   00000002EB91F1F0 3831220380 pmrbk5fdfd665 table_104_11_XYZT_0_0_0
00000006747F0478        131 my_user                   00000002EB91F1F0 3831220380 pmrbk5fdfd665 table_104_11_XYZT_0_0_0

Apparently, the string into the sql_text column is  a  HEX representation of the object_id that is being accessed.
In our case is :XYZT

SQL>    select owner, object_name, object_type
  2    from dba_objects
  3    where object_id = (select to_number('&hex_value','XXXXXX') from dual);
Enter value for hex_value: XYZT
old   3:   where object_id = (select to_number('&hex_value','XXXXXX') from dual)
new   3:   where object_id = (select to_number('XYZT','XXXXXX') from dual)

                                                                                                                    
OWNER                  OBJECT_TYPE                                               OBJECT_NAME
---------------------- --------------------------------------------------------------------------
my_user                TABLE                                                     my_table


SQL> desc my_user.my_table;
 Name                  Type
 -------------------   ----------------
 EXPERIMENT_ID          VARCHAR2(20)
 DOCUMENT               BLOB
............….

If we are looking for the retention on the ” DOCUMENT ” column, we will see :

SQL> select table_name, pctversion, retention,segment_name from dba_lobs where table_name in ('my_table');

TABLE_NAME                                                                               
                                                  PCTVERSION  RETENTION                  SEGMENT_NAME
---------------------------------------------------------------------------------------- ------------------------------------
my_table                                                       900                       SYS_LOB0000027039C00002$$

In order to fix it , try to run this column to adapt the retention of the blob column, related to the new value of the UNDO_RETENTION parameter,

ALTER TABLE my_table MODIFY LOB (DOCUMENT) (3600);

Cet article When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica.

Let’s me explain you by an example :

Users can entered different values for a user field from an OBI report:
– If no value entered then all rows must be returned.
– If 1 value entered then only row(s) related to the filter must be returned.
– If List Of Values entered then only row(s) related to the filter must be returned.

The SQL we want to write must take into account all the conditions possible (the 3 listed above).

Here is the first version of the SQL query :

select * 
from my_table a
WHERE a.pt_name LIKE decode(:PT_PARAM, NULL, '%', '')
OR a.pt_name IN (:PT_PARAM);

:PT_PARAM is the user variable.

The problem with this query is that the both conditions :
– a.pt_name LIKE decode(:PT_PARAM, NULL, ‘%’, ”)
– a.pt_name IN (:PT_PARAM)
are always TRUE, so unnecessary work will be done by oracle optimizer.

We can prove that by checking the execution plan :

If :PT_PARAM is equal to ‘Value1′ :

EXPLAIN PLAN FOR
select * 
from my_table a  
WHERE a.pt_name LIKE decode('Value1', NULL, '%', '')
OR a.pt_name IN ('Value1');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1606647163
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     5 |  1140 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE         |     5 |  1140 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                  |       |       |            |          |
|   3 |    BITMAP OR                        |                  |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE       | BIX_DMED_TERM_01 |       |       |            |          |
|   5 |     BITMAP MERGE                    |                  |       |       |            |          |
|*  6 |      BITMAP INDEX RANGE SCAN        | BIX_DMED_TERM_01 |       |       |            |          |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."PT_NAME"='Value1')
   6 - access("A"."PT_NAME" LIKE NULL)
       filter("A"."PT_NAME" LIKE NULL AND "A"."PT_NAME" LIKE NULL)

Oracle Optimizer does 2 access :
– 1 access for NULL value
– 1 access for ‘Value1′ value

The first access is not necessary since the user has selected a Not Null Value (‘Value1′). Indeed if the user select one Not Null value (‘Value1′), we don’t want oracle execute condition for NULL value.

To avoid this couple of access, it’s necessary to re-write the SQL statement like that :

select * 
from my_table a
where (:PT_PARAM is null AND a.pt_name like '%')
OR (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM));

We just add a SQL clause indicating that if the first condition is TRUE, the second condition is FALSE and vice versa:
if (:PT_PARAM is null AND a.pt_name like ‘%’) is TRUE then (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is FALSE
if (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is TRUE then (:PT_PARAM is null AND a.pt_name like ‘%’) is FALSE

Checking the execution plan related to the new SQL statement :

EXPLAIN PLAN FOR
select * 
from my_table a
where ('Value1' is null AND a.pt_name like '%')
OR ( 'Value1' IS NOT NULL AND a.pt_name in ('Value1'));

Plan hash value: 2444798625
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     5 |  1140 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE         |     5 |  1140 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                  |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE        | BIX_DMED_TERM_01 |       |       |            |          |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."PT_NAME"='Value1')

Now only one access is done, the one related to the value ‘Value1′ selected by the user.

Conclusion:

Tuning a SQL query can be made within different way : modify the physical design for a table (indexes, partitioning), influence the optimizer (Hints) to force an execution plan, modify oracle optimizer database parameters.

But very often, SQL tuning can be made “simply” by re-written the SQL query. Most of the time, performance problem is due to bad written SQL statement.

The first advice before to write a SQL query is:
– always understand the business needs in order to avoid bad interpretations.
– avoid unnecessary step for oracle optimizer by checking oracle execution plan in details to control the path oracle choose to access the data.
– avoid writing complex SQL – SQL is a very simple language, don’t forget it.

Cet article SQL Tuning – Mix NULL / NOT NULL Values est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Even you don’t need 18c, or you’re not ready for this release, this patch will also update your 11gR2, 12cR1 and 12cR2 databases to the latest PSU available on ODA, it means for example the patchset from last July if you’re using 12cR1. Here is how to apply this latest patch for your ODA lite. In this example, the patch was applied on an X6-2S ODA running on previous release: 12.2.1.4.0.

1) Download the patch

The patch number is 28864490. As usual, this patch will update the following components: dcs (odacli), operating system, bios/firmwares, storage (on lite it means data disks firmwares), ilom, GI, dbhomes and databases.
Download and copy the patch to a temporary folder on the server, for example /opt/patch. You’ll have to be root to apply the patch.

2) Check the actual versions and free space on disk

First check the current version:

odacli describe-component

System Version
---------------
12.2.1.4.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       12.2.1.4.0            up-to-date
GI                                        12.2.0.1.180417       up-to-date
DB                                        12.1.0.2.180417       up-to-date
DCSAGENT                                  18.2.1.0.0            up-to-date
ILOM                                      3.2.9.23.r116695      up-to-date
BIOS                                      38070200              up-to-date
OS                                        6.9                   up-to-date
FIRMWARECONTROLLER {
[ c2 ]                                    4.650.00-7176         up-to-date
[ c0,c1 ]                                 KPYAGR3Q              up-to-date
}
FIRMWAREDISK                              0R3Q                  up-to-date

For the moment, the “available version” column doesn’t know that a newer patch has been released.

Check that folders /, /u01 and /opt have enough free GB to process (>=20GB). This 18c patch is quite big, so don’t forget that you can extend the /u01 and /opt logical volumes online quite easily, for example if you need to increase the /opt:

lvextend -L +30G /dev/VolGroupSys/LogVolOpt
resize2fs /dev/VolGroupSys/LogVolOpt

If you never changed the logical volume configuration, about 200GB are available on local disks on this kind of ODA.

3) Prepare the patch files

You need to unzip and register the 3 unzipped files (they are zip files, too).

cd /opt/patch
unzip p28864490_183000_Linux-x86-64_1of3.zip
unzip p28864490_183000_Linux-x86-64_2of3.zip
unzip p28864490_183000_Linux-x86-64_3of3.zip
odacli update-repository -f /opt/patch/oda-sm-18.3.0.0.0-181205-server1of3.zip
odacli update-repository -f /opt/patch/oda-sm-18.3.0.0.0-181205-server2of3.zip
odacli update-repository -f /opt/patch/oda-sm-18.3.0.0.0-181205-server3of3.zip

Updating the repository, as other tasks through odacli, will generate a job. Check if the 3 latest jobs are OK:

odacli list-jobs | head -n 3;  odacli list-jobs | tail -n 4
ID                                       Description               Created                             Status
---------------------------------------- ------------------------- ----------------------------------- ----------
add82ae5-3295-49ad-811d-c8c57ebb0cb1     Repository Update         March 7, 2019 1:11:13 PM CET        Success
a057e961-2584-467f-9fc3-d8951dcae213     Repository Update         March 7, 2019 1:11:49 PM CET        Success
0483dbf8-7562-424e-b7bb-3786558d62b1     Repository Update         March 7, 2019 1:15:31 PM CET        Success

4) Run the prepatch report

It’s strongly advised to run the prepatch report before patching:

odacli create-prepatchreport -s -v 18.3.0.0.0
odacli describe-prepatchreport -i 9112e726-62f1-4e85-9d9c-aec46e8e8210
Patch pre-check report
------------------------------------------------------------------------
                 Job ID:  9112e726-62f1-4e85-9d9c-aec46e8e8210
            Description:  Patch pre-checks for [OS, ILOM, GI]
                 Status:  SUCCESS
                Created:  March 7, 2019 1:41:23 PM CET
                 Result:  All pre-checks succeeded

Node Name
---------------
dbi02

Pre-Check                      Status   Comments
------------------------------ -------- --------------------------------------
__OS__
Validate supported versions     Success   Validated minimum supported versions
Validate patching tag           Success   Validated patching tag: 18.3.0.0.0
Is patch location available     Success   Patch location is available
Verify OS patch                 Success   Verified OS patch

__ILOM__
Validate supported versions     Success   Validated minimum supported versions
Validate patching tag           Success   Validated patching tag: 18.3.0.0.0
Is patch location available     Success   Patch location is available
Checking Ilom patch Version     Success   Successfully verified the versions
Patch location validation       Success   Successfully validated location

__GI__
Validate supported GI versions  Success   Validated minimum supported versions
Validate available space        Success   Validated free space under /u01
Verify DB Home versions         Success   Verified DB Home versions
Validate patching locks         Success   Validated patching locks

Success on all the pre-checked elements is not a patching guarantee: as for each patch you’ll need to manually remove extra rpms and unsupported configurations, like public yum repository (all the package updates have to be done through ODA patches only).

5) Update the dcs-agent

It seems that it’s no more mandatory to update the dcs-agent before patching the server, it will probably be updated in the same time, but if you want it’s still working:

/opt/oracle/dcs/bin/odacli update-dcsagent -v 18.3.0.0.0

odacli update-dcsagent -v 18.3.0.0.0
{
  "jobId" : "5cd58876-3db5-48d5-880e-2ce934545d2f",
  "status" : "Created",
  "message" : "Dcs agent will be restarted after the update. Please wait for 2-3 mins before executing the other commands",
  "reports" : [ ],
  "createTimestamp" : "March 07, 2019 13:35:12 PM CET",
  "resourceList" : [ ],
  "description" : "DcsAgent patching",
  "updatedTime" : "March 07, 2019 13:35:12 PM CET"
}
odacli list-jobs | head -n 3;  odacli list-jobs | tail -n 2

ID                                       Description                         Created                             Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
5cd58876-3db5-48d5-880e-2ce934545d2f     DcsAgent patching                   March 7, 2019 1:35:12 PM CET        Success

6) Update the server

Updating the server is the biggest and longest part of the patch, make sure that everything is OK before patching.

odacli update-server -v 18.3.0.0.0
odacli describe-job -i "3a568fb1-8517-405f-9a60-0a1ee285e1ff"

Job details
----------------------------------------------------------------
                     ID:  3a568fb1-8517-405f-9a60-0a1ee285e1ff
            Description:  Server Patching
                 Status:  Running
                Created:  March 7, 2019 4:48:32 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Patch location validation                March 7, 2019 4:48:32 PM CET        March 7, 2019 4:48:32 PM CET        Success
dcs-controller upgrade                   March 7, 2019 4:48:32 PM CET        March 7, 2019 4:48:32 PM CET        Success
Patch location validation                March 7, 2019 4:48:32 PM CET        March 7, 2019 4:48:32 PM CET        Success
dcs-cli upgrade                          March 7, 2019 4:48:33 PM CET        March 7, 2019 4:48:33 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:33 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:38 PM CET        Success
Updating YumPluginVersionLock rpm        March 7, 2019 4:48:38 PM CET        March 7, 2019 4:48:39 PM CET        Success
Applying OS Patches                      March 7, 2019 4:48:39 PM CET        March 7, 2019 4:51:31 PM CET        Success
Creating repositories using yum          March 7, 2019 4:51:31 PM CET        March 7, 2019 4:51:32 PM CET        Success
Applying HMP Patches                     March 7, 2019 4:51:32 PM CET        March 7, 2019 4:51:32 PM CET        Success
Patch location validation                March 7, 2019 4:51:32 PM CET        March 7, 2019 4:51:32 PM CET        Success
oda-hw-mgmt upgrade                      March 7, 2019 4:51:33 PM CET        March 7, 2019 4:51:33 PM CET        Success
Applying Firmware Disk Patches           March 7, 2019 4:51:33 PM CET        March 7, 2019 4:51:51 PM CET        Success
Applying Firmware Expander Patches       March 7, 2019 4:51:51 PM CET        March 7, 2019 4:52:03 PM CET        Success
Applying Firmware Controller Patches     March 7, 2019 4:52:03 PM CET        March 7, 2019 4:52:16 PM CET        Success
Checking Ilom patch Version              March 7, 2019 4:52:17 PM CET        March 7, 2019 4:52:19 PM CET        Success
Patch location validation                March 7, 2019 4:52:19 PM CET        March 7, 2019 4:52:20 PM CET        Success
Save password in Wallet                  March 7, 2019 4:52:21 PM CET        March 7, 2019 4:52:21 PM CET        Success
Apply Ilom patch                         March 7, 2019 4:52:21 PM CET        March 7, 2019 4:52:22 PM CET        Success
Copying Flash Bios to Temp location      March 7, 2019 4:52:22 PM CET        March 7, 2019 4:52:22 PM CET        Success
Starting the clusterware                 March 7, 2019 4:52:22 PM CET        March 7, 2019 4:52:22 PM CET        Success
Creating GI home directories             March 7, 2019 4:52:22 PM CET        March 7, 2019 4:52:22 PM CET        Success
Cloning Gi home                          March 7, 2019 4:52:22 PM CET        March 7, 2019 4:54:55 PM CET        Success
Configuring GI                           March 7, 2019 4:54:55 PM CET        March 7, 2019 4:55:54 PM CET        Success
Running GI upgrade root scripts          March 7, 2019 4:55:54 PM CET        March 7, 2019 5:08:19 PM CET        Failure

Bad news, GI upgrade failed in my case. Hopefully it’s possible to relaunch the patching and it will skip the already patched components. But another attempt failed 2 steps before (Cloning the Gi home: for sure GI home is already deployed on disk).

Actually if you already patched your ODA with the previous release, there is a bug with this previous patch:

ODA GI Patching from 12.2 to 18.3 Failed, CLSRSC-697: Failed to get the value of environment variable ‘TZ’ from the environment file (Doc ID 2502972.1)

An XML file incorrectly describing the previous Grid Infrastructure home needs to be deleted before applying this newest patch:

cat '/u01/app/grid/crsdata/@global/crsconfig/ckptGridHA_global.xml' | grep 12
         <PROPERTY NAME="VERSION" TYPE="STRING" VAL="12.2.0.1.0"/>
         <PROPERTY NAME="OLD_CRS_HOME" TYPE="STRING" VAL="/u01/app/12.1.0.2/grid"/>
         <PROPERTY NAME="OLD_CRS_VERSION" TYPE="STRING" VAL="12.1.0.2.0"/>
         <PROPERTY NAME="MANUAL_BACKUP_FILE_NAME" TYPE="STRING" VAL="+DATA:/dbi02-c/OCRBACKUP/dbi02-c_backup12.1.0.2.0.ocr.261.987245221"/>
rm '/u01/app/grid/crsdata/@global/crsconfig/ckptGridHA_global.xml'

Before running the patch again, cloned GI home needs to be deleted and its reference in the oraInventory needs to be removed:

vi /u01/app/oraInventory/ContentsXML/inventory.xml
Delete this line ==>  <HOME NAME="OraGrid180" LOC="/u01/app/18.0.0.0/grid" TYPE="O" IDX="7" CRS="true"/> 

rm -rf /u01/app/18.0.0.0

Now the patching will work:

odacli update-server -v 18.3.0.0.0
odacli describe-job -i "3a568fb1-8517-405f-9a60-0a1ee285e1ff"

Job details
----------------------------------------------------------------
                     ID:  3a568fb1-8517-405f-9a60-0a1ee285e1ff
            Description:  Server Patching
                 Status:  Success
                Created:  March 7, 2019 5:48:32 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Patch location validation                March 7, 2019 5:48:32 PM CET        March 7, 2019 5:48:32 PM CET        Success
dcs-controller upgrade                   March 7, 2019 5:48:32 PM CET        March 7, 2019 5:48:32 PM CET        Success
Patch location validation                March 7, 2019 5:48:32 PM CET        March 7, 2019 5:48:32 PM CET        Success
dcs-cli upgrade                          March 7, 2019 5:48:33 PM CET        March 7, 2019 5:48:33 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:33 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Creating repositories using yum          March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:38 PM CET        Success
Updating YumPluginVersionLock rpm        March 7, 2019 5:48:38 PM CET        March 7, 2019 5:48:39 PM CET        Success
Applying OS Patches                      March 7, 2019 5:48:39 PM CET        March 7, 2019 5:51:31 PM CET        Success
Creating repositories using yum          March 7, 2019 5:51:31 PM CET        March 7, 2019 5:51:32 PM CET        Success
Applying HMP Patches                     March 7, 2019 5:51:32 PM CET        March 7, 2019 5:51:32 PM CET        Success
Patch location validation                March 7, 2019 5:51:32 PM CET        March 7, 2019 5:51:32 PM CET        Success
oda-hw-mgmt upgrade                      March 7, 2019 5:51:33 PM CET        March 7, 2019 5:51:33 PM CET        Success
Applying Firmware Disk Patches           March 7, 2019 5:51:33 PM CET        March 7, 2019 5:51:51 PM CET        Success
Applying Firmware Expander Patches       March 7, 2019 5:51:51 PM CET        March 7, 2019 5:52:03 PM CET        Success
Applying Firmware Controller Patches     March 7, 2019 5:52:03 PM CET        March 7, 2019 5:52:16 PM CET        Success
Checking Ilom patch Version              March 7, 2019 5:52:17 PM CET        March 7, 2019 5:52:19 PM CET        Success
Patch location validation                March 7, 2019 5:52:19 PM CET        March 7, 2019 5:52:20 PM CET        Success
Save password in Wallet                  March 7, 2019 5:52:21 PM CET        March 7, 2019 5:52:21 PM CET        Success
Apply Ilom patch                         March 7, 2019 5:52:21 PM CET        March 7, 2019 5:52:22 PM CET        Success
Copying Flash Bios to Temp location      March 7, 2019 5:52:22 PM CET        March 7, 2019 5:52:22 PM CET        Success
Starting the clusterware                 March 7, 2019 5:52:22 PM CET        March 7, 2019 5:52:22 PM CET        Success
Creating GI home directories             March 7, 2019 5:52:22 PM CET        March 7, 2019 5:52:22 PM CET        Success
Cloning Gi home                          March 7, 2019 5:52:22 PM CET        March 7, 2019 5:54:55 PM CET        Success
Configuring GI                           March 7, 2019 5:54:55 PM CET        March 7, 2019 5:55:54 PM CET        Success
Running GI upgrade root scripts          March 7, 2019 5:55:54 PM CET        March 7, 2019 6:08:19 PM CET        Success
Resetting DG compatibility               March 7, 2019 6:08:19 PM CET        March 7, 2019 6:08:29 PM CET        Success
Running GI config assistants             March 7, 2019 6:08:29 PM CET        March 7, 2019 6:11:50 PM CET        Success
restart oakd                             March 7, 2019 6:11:53 PM CET        March 7, 2019 6:12:03 PM CET        Success
Updating GiHome version                  March 7, 2019 6:12:03 PM CET        March 7, 2019 6:12:09 PM CET        Success
Setting AUDIT SYSLOG LEVEL               March 7, 2019 6:12:30 PM CET        March 7, 2019 6:14:55 PM CET        Success
Update System version                    March 7, 2019 6:14:55 PM CET        March 7, 2019 6:14:55 PM CET        Success
preRebootNode Actions                    March 7, 2019 6:14:55 PM CET        March 7, 2019 6:15:37 PM CET        Success
Reboot Ilom                              March 7, 2019 6:15:37 PM CET        March 7, 2019 6:15:37 PM CET        Success

Once this part of the patch is successfully applied, let the server reboot automatically 5 minutes after and then check again the components:

odacli describe-component

System Version
---------------
18.3.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       18.3.0.0.0            up-to-date
GI                                        18.3.0.0.180717       up-to-date
DB                                        12.1.0.2.180417       12.1.0.2.180717
DCSAGENT                                  18.3.0.0.0            up-to-date
ILOM                                      4.0.4.22.r126940      up-to-date
BIOS                                      38110100              up-to-date
OS                                        6.10                  up-to-date
FIRMWARECONTROLLER {
[ c2 ]                                    4.650.00-7176         up-to-date
[ c0,c1 ]                                 KPYAGR3Q              kpyair3q
}
FIRMWAREDISK                              0R3Q                  up-to-date

7) Patch the storage

You now need to patch the storage, understand firmware of data disks:

odacli update-storage -v 18.3.0.0.0
{
  "jobId" : "38a779cf-ac6c-4514-a838-f5cd1bec637c",
  "status" : "Created",
  "message" : "Success of Storage Update may trigger reboot of node after 4-5 minutes. Please wait till node restart",
  "reports" : [ ],
  "createTimestamp" : "March 07, 2019 17:28:34 PM CET",
  "resourceList" : [ ],
  "description" : "Storage Firmware Patching",
  "updatedTime" : "March 07, 2019 17:28:35 PM CET"
}

odacli describe-job -i "38a779cf-ac6c-4514-a838-f5cd1bec637c"
Job details
----------------------------------------------------------------
                     ID:  38a779cf-ac6c-4514-a838-f5cd1bec637c
            Description:  Storage Firmware Patching
                 Status:  Success
                Created:  March 7, 2019 5:28:34 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Applying Firmware Disk Patches           March 7, 2019 5:28:35 PM CET        March 7, 2019 5:28:49 PM CET        Success
Applying Firmware Controller Patches     March 7, 2019 5:28:49 PM CET        March 7, 2019 5:33:02 PM CET        Success
preRebootNode Actions                    March 7, 2019 5:33:02 PM CET        March 7, 2019 5:33:02 PM CET        Success
Reboot Ilom                              March 7, 2019 5:33:02 PM CET        March 7, 2019 5:33:02 PM CET        Success

odacli describe-component
System Version
---------------
18.3.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       18.3.0.0.0            up-to-date
GI                                        18.3.0.0.180717       up-to-date
DB                                        12.1.0.2.180417       12.1.0.2.180717
DCSAGENT                                  18.3.0.0.0            up-to-date
ILOM                                      4.0.4.22.r126940      up-to-date
BIOS                                      38110100              up-to-date
OS                                        6.10                  up-to-date
FIRMWARECONTROLLER {
[ c2 ]                                    4.650.00-7176         up-to-date
[ c0,c1 ]                                 KPYAIR3Q              up-to-date
}
FIRMWAREDISK                              0R3Q                  up-to-date

The server will also reboot after this update.

8) Patch the dbhomes

You now need to patch the dbhomes separately. First of all, list them:

odacli list-dbhomes

ID                                       Name                 DB Version        Home Location                                 Status
---------------------------------------- -------------------- ----------------- ------------------------------------------- ----------
2c28acde-f041-4283-b984-fe6b73dd724d     OraDB12102_home2     12.1.0.2.180417   /u01/app/oracle/product/12.1.0.2/dbhome_2     Configured
ebac9543-337b-4edd-8e00-d593abd52ca6     OraDB12102_home10    12.1.0.2.180417   /u01/app/oracle/product/12.1.0.2/dbhome_10    Configured
0ab15f33-e1b7-4193-a110-fa4aee01cc21     OraDB12102_home21    12.1.0.2.180417   /u01/app/oracle/product/12.1.0.2/dbhome_21    Configured

Patch the dbhomes one by one. Target version is actually version of the patch. Remember that updating the ODA through a patch will never upgrade your database to a newer release. Only the PSU number will change (the fifth number which is actually a date).

odacli update-dbhome -i 2c28acde-f041-4283-b984-fe6b73dd724d -v 18.3.0.0.0
{
  "jobId" : "bc3e5564-7283-4497-ac70-9e41c834183d",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "March 07, 2019 17:45:46 PM CET",
  "resourceList" : [ ],
  "description" : "DB Home Patching: Home Id is 2c28acde-f041-4283-b984-fe6b73dd724d",
  "updatedTime" : "March 07, 2019 17:45:46 PM CET"
}
odacli describe-job -i "bc3e5564-7283-4497-ac70-9e41c834183d"

Job details
----------------------------------------------------------------
                     ID:  bc3e5564-7283-4497-ac70-9e41c834183d
            Description:  DB Home Patching: Home Id is 2c28acde-f041-4283-b984-fe6b73dd724d
                 Status:  Success
                Created:  March 7, 2019 5:45:46 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
clusterware patch verification           March 7, 2019 5:45:47 PM CET        March 7, 2019 5:45:52 PM CET        Success
Patch location validation                March 7, 2019 5:45:52 PM CET        March 7, 2019 5:46:03 PM CET        Success
Opatch updation            ..
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The scope of this blog will be to show how to create a primary role database based on a backup of a standby database on 12cR2.

Step1: We are assuming that an auxiliary instance has been created and started in nomount mode.

rman target /
restore primary controlfile from 'backup_location_directory/control_.bkp';
exit;

By specifying “restore primary” , will modify the flag into the controlfile, and will mount a primary role instance instead of a standby one.

Step2: Once mounted the instance, we will restore the backup of the standby database.

run
{
catalog start with 'backup_location_directory';
restore database;
alter database flashback off;
recover database 
}

If in the pfile used to start the instance, you specified the recovery destination and size parameters it will try to enable the flashback.
The flashback enable , before during the recovery is not allowed, so we will deactivate for the moment.

Step3: Restore/recover completed successfully, we will try to open the database, but got some errors:

alter database open :

ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

Step4: Fix the errors and try to open the database:

--normal redo log groups
alter database clear unarchived logfile group YYY;

--standby redo log groups
alter database clear unarchived logfile group ZZZ;
alter database drop logfile group ZZZ;

Is not enough. Looking on the database alert log file we can see :

LGWR: Primary database is in MAXIMUM AVAILABILITY mode 
LGWR: Destination LOG_ARCHIVE_DEST_2 is not serviced by LGWR 
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR 

Errors in file /<TRACE_DESTINATION>_lgwr_1827.trc: 
ORA-16072: a minimum of one standby database destination is required 
LGWR: terminating instance due to error 16072 
Instance terminated by LGWR, pid = 1827

Step5: Complete the opening procedure:

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
alter database set standby database to maximize performance;

SQL> select name,open_mode,protection_mode from v$database;

NAME      OPEN_MODE            PROTECTION_MODE
--------- -------------------- --------------------
NAME      MOUNTED              MAXIMUM PERFORMANCE

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
NAME      NAME_UNIQUE                    PRIMARY

Cet article Create a primary database using the backup of a standby database on 12cR2 est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle released a new way to search for database features, and it is …

..really great.
Try out the cool new search application for Oracle database features.

What is special about? The new site gives you a very smart overview of the database features and in which version they are available (at least from 11-19)

As example, when you search for “compression” it shows you this output:

With a click on the feature you are interessted in you can see a short description and jump directly into the documentation

Oh and when somebody from Oracle is reading this:
Please add also the license information for all the features: Thank you

Playing around with the new site, I already found some features I did not know that they exist.
Or did you know, there is an ASM Cloning feature starting with Oracle 18c and Flex Disk groups?

Have a nice weekend.

P.S.
Thanks to
Thomas Teske
from Oracle who showed me this feature!

Cet article New search function for Oracle database features est apparu en premier sur Blog dbi services.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The Italian Oracle User Group (ITOUG) is an independent group of Oracle enthusiasts and experts which work together as volunteers to promote technical knowledge sharing in Italy.

Here the ITOUG Board members:

This year ITOUG Tech Days take place in Milan on 30th January and in Rome on 1st February. Two different streams for each event:
– Database
– Analytics and Big Data
Today I participated to the event in Milan.
But before talking about that, ITOUG Tech Days started with the speakers’ dinner on Tuesday evening in Milan: aperitif, good Italian food and very nice people.

On Wednesday morning, we all met at Oracle Italia in Cinisello Balsamo (MI):

After the welcome message by some ITOUG Board members:

sessions finally started. I attended the following ones of the Database stream:

- “Instrumentation 2.0: Performance is a feature” by Lasse Jenssen from Norway

We have to understand what’s going on into a system, performance is a feature and we need instrumentation. Oracle End-to-End metrics, new tags in 12c, v$sql_monitor, dbms_monitor… And work in progress for instrumentation 3.0 with ElasticSearch, LogStash and Kibana.

- “Hash Join Memory Optimization” by one of the ITOUG Board member, Donatello Settembrino

How Hash Join works and how to improve PGA consumption performances. Examples of partitioning (to exclude useless data), (full) Partitioning Wise Join (to use less resources) and parallelism. Differences between Right-Deep Join Trees and Left-Deep Join Trees, and concept of Bushy Join Trees in 12R2.

- “Design your databases using Oracle SQL Developer Data Modeler” by Heli Helskyaho from Finland

Oracle SQL Developer Data Modeler with SQL Developer or in a standalone mode to design your database. It uses Subversion integrated in the tool for the version control and management. It also has support for other databases, MySQL for example. And it’s free.

- “Bringing your Oracle Database alive with APEX” by Dimitri Gielis from Belgium

Two things to learn from this session:
1) Use Oracle Application Express to design and develop a web application.
2) And Quick SQL to create database objects and build a data model
And all that in a very fast way.

- “Blockchain beyond the Hype” by one of the ITOUG Board member, Paolo Gaggia

The evolution of blockchain from bitcoin to new Enterprise-Oriented implementation and some interesting use cases.

Every session was very interesting: thanks to the great and amazing speakers (experts working on Oracle technologies, Oracle ACE, Oracle ACE Director…) for their sharing.

Follow the Italian Oracle User Group on Twitter (IT_OUG) and see you at the next ITOUG event!

Cet article Italian Oracle User Group Tech Days 2019 est apparu en premier sur Blog dbi services.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview