UKOUG Tech17 Dec 3rd-6th

I love the UKOUG Tech conferences, but then I’m biased. It was the first conference I spoke at a few years ago, but I’ve been going to see UKOUG events since the mid 1990’s. At my first event there was a DBA talk about VLDB – the database was 40GB and growing at 1GB per month. We were all amazed at the huge size of the database!

This year I was the lead putting together Tech17; want to know what it’s all about?
Watch this short video

and look at the agenda here – 3 days of excellent technical content, plus SuperSunday!

Hope to see you there!

UKOUG Northern Technology Summit

northerntech17-com-600x110-cfp-v1

Here at the UKOUG, we’ve just announced that we will be holding a 1-day UKOUG Northern Technology Summit in the North of England on 18th April 2018.

It will be in Leeds, at the Park Plaza!

This is a 5-stream 1 day event, with around 30 talks! We’d call it a conference – it’s bigger than some European national conferences! – but we don’t want to have any confusion with our sexy flagship conference, UKOUG Tech17 (or, indeed Tech18 in Liverpool!)

The Call For Papers is now Open, and we are looking for talks in relation to Database, RAC, Cloud, Apex, Development and Engineered Systems. A full list can be found on the CfP page, linked here.

Tweet about it with the hashtag #UKOUG_ntech

HrOUG – The Croatian Conference

Arriving on Tuesday afternoon, directly from the Slovenia Conference and it’s straight into the 3rd Autumn conference! Conveniently, Slovenia and Croatia are timed together and are held within a little over 1 hours drive (plus a 15 minute boat ride!) apart. Well done on that one guys!IMG_1892

A quiet beer as many speakers and delegates wait at the docks for the boat to arrive, and I seem to be timing these sunsets well!IMG_1795

Wednesday was a day off, so Tuesday night allowed me to let my hair down a little and have a drink until late into the night. Wedneday, and the organizer, Davor, had arranged for all of the Oracle ACEs and Oakies to go on a tour of nearby Pula followed by some Go Karting. Great fun! I knew nothing of Pula so I was rather surprised to discover a huge Amphitheatre there!

This slideshow requires JavaScript.

The talks, on Thursday and Friday, were a repeat of the talks in Slovenia about Histograms and Plan Stability.  A great conference in a truely excellent location – having the conference on an island gives it the same magic as the OUGN conference: you can’t just wander off, we are all there together and that really adds to the conference.

IMG_1933

Rovinj with Andrew!

As a added bonus, one of my friends happened to be in the nearby town of Rovinj on the Friiday afternoon, which meant a serendipidous meet-up for lunch.

Overall a wonderful pair of conferences which felt as much a holiday as work, although I think I need a holiday to recover from them.

SiOUG – The Slovenian Conference

So October came around and it was time for my second and third conferences of the autumn season.  I had been kindly select to do 2 talks at this Slovenian User Group, but a last-minute problem with another speaker meant I found myself giving 3 talks in 2 days. Excellent fun!IMG_1728

 

I arrived in lovely Portoroz just as the sun was setting, with what seemed like most of Slovenia leaving the coast on the final hot Sunday of the year, just in time for a huge and wonderful dinner with all of the other speakers.

Day 1 was a whirlwind of talks, food and presenting  much of what I know about Histograms and Chocolate, washed down with a local craft ale – Thank You HumanFish Brewery! Yum!

A party at the end of day 1, with a generous consumption of wine and Craft Ale – all consumed with the lovely friendly and fun Slovenians at the conference – y’all know who you are! – meant that Day 2 held some trepidation…. would I wake up in time!

Day 2 saw me giving 2 more talks, about Optimizer Plan Stability, and also about DataGuard, in front of an interested and good sized audience before hopping into my car and driving down to Croatia to give 2 talks in a somewhat more leisurely 3 days. Tomorrow is a day off! Holiday!

UKOUG RAC SIG, September 27th

There’s a UKOUG RAC SIG on the 27th of September in London.

There a range of great speakers talking about topics from the new RAC Domain Clusters, 12.2 Upgrading, Patching Exadata, and much more.

It’ll be great, it’s true! See you there…

Online Index Rebuild Problem in 12C/18C/19C

When building an index in Oracle Enterprise edition using the keyword “ONLINE”, if the index build fails for whatever reason a messy set of extents can get left behind.

In Oracle 12.1 [update still there in 12.2 and 18C and 19.6] there’s a job (located in DBA_SCHEDULER_JOBS) to help with this: “SYS”.”CLEANUP_ONLINE_IND_BUILD” which ticks away, undocumented, in the background cleaning up “rogue” extents once an hour, every hour (start time based upon job creation time)However, if I run a CREATE INDEX … ONLINE command on a table and the index creation happens to coincide with the invocation of the cleanup job I get the following error in my alert log

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl1:
*************************************************************************
2017-05-12 05:28:37.191123 -04:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_j000_12235.trc:
ORA-12012: error on auto execute of job "SYS"."CLEANUP_ONLINE_IND_BUILD"
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_PDB", line 76
ORA-06512: at line 4

Oh no! The job has failed to clean up the extents that I was currently using. Phew!

The CREATE INDEX … ONLINE command completed with success a few minutes later.

A few minutes after that, an update statement failed with:

ORA-08102 index key not found for obj# 107102 file 16 block 1171234

Yes, I have a corrupt index! index_fadeMy assumption is that the cleanup job had removed all of the extents which had been allocated before the cleanup job started. The index create statement did not notice as the current extent removal attempt blocked (hence the job error) and future to-be created extents were not affected. However, most of the index had been “cleaned up”. #sadface

Solution: re-create the index and make sure the index creation is complete before your hourly job kicks in. Better still, disable the undocumented hourly job… make sure get the full backing of an Oracle Support SR first!

Next Step: Raise SR with Oracle about this!

Version Check 12/11/2018:

select banner_full from v$version

BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production
Version 18.1.0.0.0


21:17:19 SYS @ ORCL > select owner,job_name,start_date,repeat_interval,state from dba_scheduler_jobs where job_name = 'CLEANUP_ONLINE_IND_BUILD';

OWNER JOB_NAME START_DATE REPEAT_INTERVAL STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------------ ---------------
SYS CLEANUP_ONLINE_IND_BUILD 07-FEB-18 08.13.44.415343 PM +00:00 FREQ = HOURLY; INTERVAL = 1 SCHEDULED

 

 

MOS Solution Note 2280374.1 is not helpful:

Solution

Rebuild the index at a time other than the time frame when “SYS”.”CLEANUP_ONLINE_IND_BUILD” is running as auto job. #facepalm #whatifittakesover1hourtobuild

 

GoldenGate unsupported types

identity
When using GoldenGate there are few rules which need to be followed, and a very small number of unsupported datatypes. This is the excerpt from the GoldenGate 12.2 manual:

 

 

1.6.10 Non-Supported Oracle Data Types
Oracle GoldenGate does not support the following data types.

 ANYDATA fetch-based column support for data types with VARRAYS that do not include named collections and VARRAYS embedded within those data types
 ANYDATASET
 ANYTYPE
 BFILE
 MLSLABEL
 ORDDICOM
 REFs
 TIMEZONE_ABBR
 URITYPE
 UDT with containing an unsupported Oracle Data Type
 Oracle GoldenGate does not support replication of identity column data or Valid Time Temporal column data.

One of those may be more common that you think – the last bullet point is the Oracle 12C datatype IDENTITY. The following code snippet is not supported for replication by GoldenGate

create table with_identity
( COL_ID    number(38) generated always as identity minvalue 1 not null enable,
  text_info varchar2(10),
  constraint with_identity_pk  primary key (COL_ID) );

insert into with_identity (text_info) values ('Row 1');

commit;

desc with_identity
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 COL_ID                        NOT NULL NUMBER(38)
 TEXT_INFO                              VARCHAR2(10)

You can see that it is an identity column in user_tab_columns:

select TABLE_NAME,COLUMN_NAME,IDENTITY_COLUMN from user_tab_columns where table_name = 'WITH_IDENTITY';

TABLE_NAME                     COLUMN_NAME                    IDE
------------------------------ ------------------------------ ---
WITH_IDENTITY                  COL_ID                         YES
WITH_IDENTITY                  TEXT_INFO                      NO

So, how does GoldenGate alert you to the fact that it will not be replicating this table with an unsupported type? It doesn’t.

look in ggserr.log – nothing
look in the extract report file for wildcard matching the table – nothing

With a table containing an unsupported data type, the lack of replication is silent because GoldenGate is completely ignoring the object. Completely Silently.

So, how could I have picked this up before it became a problem?

Obviously I need to run the script in MOS article 1296168.1, which analyzes your schema for GoldenGate compatiblity. Unfortunately, Oracle have not updated this to account for IDENTITY columns, so there is no indication that it does not work!

The script to run is the GoldenGate Integrated Extract Healthcheck script, found in MOS article 1448324.1

Finally some vague and unclear indication that we may have a problem. Part way through the report we get this:

++ TABLES SUPPORT BY GOLDENGATE Integrated Capture ++
Lists tables that can not be supported by OGG (NONE)
Lists table that are supported via OGG FETCH (ID KEY)

Owner           OBJECT_NAME             SUPPOR  Container ID
C##GOLDENGATE 	AQ$_OGG$Q_TAB_E_HR_C 	NONE 	1
C##GOLDENGATE 	AQ$_OGG$Q_TAB_E_HR_D 	NONE 	1
C##GOLDENGATE 	OGG$Q_TAB_E_HR 	        NONE 	1
HR              WITH_IDENTITY         ID KEY     3
IX 	        AQ$_ORDERS_QUEUETABLE_G NONE 	3

Wow.
Always take care with GoldenGate – it’s not just a plug-in solution. You need to think about the implementaiton and work with the application to get the best outcomes.

Getting Started with GoldenGate

gettingstartedwithgoldengateNot a blog post per-se, but a link to my article in the UKOUG quarterly magazine OracleScene. It is a great publication with some simply fantastic articles. Its completely free, and is available online as well as in print at Oracle offices in the UK and at UKOUG events. My article will take you through a simple step-by-step Oracle centric one way replication on VM Virtual Box. From nothing to fully working replication. Sexy! Hope you enjoy it.

Oracle Audit Part 4 – Unified Auditing Problem

Part 4 in a loose series of blog posts about auditing; the new Oracle 12 feature. Unified auditing.monkey_examine

The intention behind Unified Auditing, as the name suggests, is to pull together all of the audit records from the disparate Oracle auditing locations into a single unified location. It captures standard audit information (including sys records normally written to the O/S),  FGA audit, record from Database Vault, Label Security, rman, etc.

Sounds great, and it is. 12.1 has a significant problem, but it seems a lot better in 12.2 (where I have not yet had time to spot any problems 🙂 )

The first thing that you need to know is that it is on by default.
It is gathering audit information in your 12C database right now, possibly duplicating any traditional auditing you may have switched-on yourself. By default the database is in “Mixed mode”, meaning you can use the old style of audit and unified auditing too. You need to re-link the oracle binary with the database down to enable exclusively-Unified auditing

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

(on windows, you need to move the correct DLL into place.)

Have a quick check of the oracle executable to see if it there. If the DB is up against an ORACLE_HOME, you can see if you are in exclusive mode using:
21:41:24 SYS @ UTF8 > SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

if the DB is not up (e.g. a new home you are just building) you can locate an enable string....

[ the following is not guaranteed to work ] 
grep "sopts_IsUnifiedAuditOn" $ORACLE_HOME/bin/oracle; echo $?
Binary file /u01/app/oracle/product/19/db_1/bin/oracle matches
0
(you will get return code 1 if it's not there, and so in mixed-mode)

So what is enabled by default?

1  select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME            ENABLED ENABLED_OPTION  ENTITY_NAME     ENTITY_ SUC FAI
--------------- ---------------------- ------- --------------- --------------- ------- --- ---
ALL USERS       ORA_SECURECONFIG       BY      BY USER         ALL USERS       USER    YES YES
ALL USERS       ORA_LOGON_FAILURES     BY      BY USER         ALL USERS       USER    NO  YES

What does that mean?

  1  select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE
  2    from audit_unified_policies
  3   where policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
  4*  order by 1,2,3,4,5;

POLICY_NAME          AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCHEMA             OBJECT_NAME               OBJECT_TYPE
-------------------- ---------------------------------------- ------------------ ------------------------- ------------------------- --------------------
ORA_LOGON_FAILURES   LOGON                                    STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER DATABASE LINK                      STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER PROFILE                            STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER ROLE                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     ALTER USER                               STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     BECOME USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DATABASE LINK                     STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE DIRECTORY                         STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PROFILE                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE ROLE                              STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     CREATE USER                              SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DATABASE LINK                       STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP DIRECTORY                           STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PROFILE                             STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP ROLE                                STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     DROP USER                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      REMOTE_SCHEDULER_AGENT    ADD_AGENT_CERTIFICATE     PROCEDURE
ORA_SECURECONFIG     EXECUTE                                  OBJECT ACTION      SYS                       DBMS_RLS                  PACKAGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     LOGMINING                                SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE                      NONE                      NONE
ORA_SECURECONFIG     SET ROLE                                 STANDARD ACTION    NONE                      NONE                      NONE
ORA_SECURECONFIG     TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE                      NONE                      NONE

49 rows selected.

So that’s quite a lot of auditing on by default.

If you want to disable unified auditing policies, you need to use:

noaudit policy 
e.g. noaudit policy ora_secureconfig

So what’s the problem with Unified Auditing in 12.1?

Performance. It’s dreadful, and simply unusable.  Hwo about this simple select asking how much audit in the last 30 minutes from the UNIFIED_AUDIT_TRAIL view?

select count(*) from unified_audit_trail where EVENT_TIMESTAMP > systimestamp - interval '30' minutes;

It effectively blocked itself in a RAC environment, with one parallel query slave blocked by another – really not sure what was going on there but it never completed (well, I killed it after half a day).

I have seen a similar query in a non-RAC environment (or against table v$unified_audit_trail – which you really shouldn’t use as it can give an incomplete picture) run for well over an hour with a remarkably small data set. Not useful if you are trying to scrape content into an external monitor regularly…

MOS Article 2212196.1 explains how to resolve this in 12.1, by transferring all of the data to a table.

In 12.2, this is resolved by all data being stored in a table call AUDSYS.AUD$UNIFIED.
The table uses interval-based partitioning and is partitioned with a monthly interval:

...
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
 (PARTITION "AUD_UNIFIED_P0"  VALUES LESS THAN (TIMESTAMP' 2014-07-01 00:00:00')
...

Because it is a table, we can index it.It works like a table. It’s quick.

In my next audit article, I’ll look to explain how unified auditing works in a bit more depth, now that the performance no longer renders it unusable to queries.

SAN Migration and moving files with ASM

Here’s a quick post to help with LUN, SAN or File migration when using Oracle ASM.

Please note that this is just an example method, and is not a definitive guide.
You need to ensure your approach is appropriate for your environment.

Did you know that you can add and drop disks on a diskgroup in a single command.
This saves Oracle from doing 2 REBAL actions and speeds things up considerably using a lot fewer resources.

sqlplus / as sysasm;

> ALTER DISKGROUP data 
2   ADD  DISK 'ORCL:disk101','ORCL:disk102' 
3   DROP DISK 'disk042'     ,'disk043' 
4   REBALANCE POWER n;

Diskgroup altered.

You can monitor the REBAL operation:

select * from v$asm_operation;

GROUP_NUMBER OPERA PASS      STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE CON_ID
------------ ----- --------- ---- ----- ------ ----- -------- -------- ----------- ---------- ------
           6 REBAL REBALANCE  RUN     1      1    68    20397     3030           8                 0
           6 REBAL COMPAT    WAIT     1      1     0        0        0           0                 0

If you are just migrating from one SAN to another, without needing to move files between diskgroups, this is a really easy way to achieve it.

From Oracle 12.1 onwards. if you are moving Datafiles from one Diskgroup to a new Diskgroup, that’s straightforward too:

export ORACLE_SID=ORCL
sqlplus  / as sysdba

alter database move datafile '+DATA_DG/ORCL/DATAFILE/system.101.902468275' to '+NEW_DG';

Moving logfiles involves a drop and re-create, but it still fully online.
Take care if using DataGuard that you do not try to drop logfiles before they are archived and applied to the standby:

alter database  add logfile member '+NEW_DG' to group 1;
alter database drop logfile member '+DATA_DG/ORCL/ONLINELOG/group_1.102.902049284';

Moving tempfiles can be tricky. The default gets used really quickly after startup.

You need to add a tempfile and then get all processes to not be using the old tempfile before you can drop it [select * from gv$sort_usage]. Worst-case, this may involve a SHUTDOWN, then a STARTUP RESTRICT to stop processes connecting. Alternatively, create a new default TEMP tablespace and convince processes to use that one.

alter tablespace temp add  tempfile '+NEW_DG';
alter tablespace temp drop tempfile '+DATA_DG/ORCL/TEMPFILE/temp.204.992482742';

Moving controlfiles requires a stop and start of the database. There’s no on-line options here. I tend to do the following:

srvctl stop database -d ORCL

sqlplus / as sysdba
startup nomount
show parameter control_files


rman target /
restore controlfile to '+NEW_DG' from '+DATA_DG/ORCL/CONTROLFILE/current.291.939274434';

sqlplus / as sysdba
alter system set control_files='+NEW_DG/ORCL/CONTROLFILE/current.992.346677889'
                              ,'+FRA_DG/ORCL/CONTROLFILE/current.???.?????????'
                               scope=spfile sid='*';
shutdown

srvctl start database -d ORCL

To move your spfile in ASM is a 2-stage process which will automatically update your database config in grid control:

sqlplus / as sysdba
create  pfile='/tmp/ORCL.ora' from spfile;
create spfile='+NEW_DG'       from pfile='/tmp/ORCL.ora';

Moving a password file in ASM is straightforward too:
NOTE: you are not allowed to move OMF files names and you must copy files via their alias.

as grid:
asmcmd
cp +DATA_DG/ORCL/orapworcl +NEW_DG/ORCL/orapworclexit
as oracle:
srvctl modify database -d ORCL -pwfile +NEW_DG/ORCL/orapworclsrvctl config database -d ORCL | grep Password

If you are using Block Change Tracking, you may need to move that file too:

alter database disable block change tracking;
alter database enable block change tracking using file '+NEW_DG';

Finally, if you’re dropping disk groups don’t forget to modify the disk group dependencies in Grid Infra so your DB isn’t dependent upon groups you have now removed:

srvctl config datbase -d ORCL | grep Group
srvctl modify database -d ORCL -diskgroup 'NEW_DG,FRA_DG,other_DG'

I hope you found this quick guide useful.