The Horror Of Restore Validate For SBT_TAPE Backups

For the database environment, there are database backups to disk and tape.

I wanted to validate tape backup by using validate preview summary device type=SBT_TAPE.

I hit a brick wall as the restore was reading backups from both DISK and SBT_TAPE.

Here is the command used:

restore database until time "TRUNC(sysdate)" validate preview summary device type=SBT_TAPE;

Oracle support came through with the following info:

Reading from Disk because of the preview command.

The command is now replaced with:

restore database   until time "TRUNC(sysdate)" validate device type=SBT_TAPE;
restore archivelog until time "TRUNC(sysdate)" validate device type=SBT_TAPE;

Another option is to use RETENTION POLICY.

RMAN> show RETENTION POLICY;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> restore archivelog from time='SYSDATE-7' validate device type=SBT_TAPE;

Verify missing archivelog backup.

--- list backup of archivelog sequence 89961 thread 2;

RMAN-06025: no backup of archived log for thread 2 with sequence 3712 and starting SCN of 11284417423066 found to restore

How to Check/Validate that RMAN Backups Are Good (Doc ID 466221.1)

--- Report of backups that are used to do the restore and recover:
restore database preview;

--- Check the backup pieces are good:
restore database validate;

Works beautifully and learned something new today.

Advertisement

Data Pump Compatible Version

Import failed as shown below:

Import: Release 18.0.0.0.0 - Production on Fri Jun 4 13:07:19 2021
Version 18.6.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0.0 not compatible with target version 12.1.0.2.0

Here are the compatible settings for source and target.

Source is 18.6.0.0.0 with compatible=18.0.0.0
Target is 18.6.0.0.0 with compatible=12.1.0.2

Run export with version.

expdp version=12.1.0.2

That’s all folks.

Remove Characters From String Using regexp_replace

It’s probably have been half a century since I have coded PL/SQL.

So there I was, reviewing PL/SQL code and it looks rather redundant.

Did you know there is defined variable USER for PL/SQL code and using sys_context is not necessary?

Also, regexp_replace can be used to remove all characters from string.

Here is an example:

Basically, one line of code replaces all the redundant code.

I have no idea why there is a need to extract characters.

DINH099PD@ORCLPDB1 > show user
USER is "DINH099PD"
DINH099PD@ORCLPDB1 > @dinh.sql
DINH099PD@ORCLPDB1 > DECLARE
  2    -- Remove character from l_user
  3    l_user VARCHAR2(30) := regexp_replace(USER, '[[:alpha:]]|_');
  4    l_user2 varchar2(20);
  5    l_output varchar2(100);
  6  BEGIN
  7    -- Remove character from l_user2
  8    l_user2 := sys_context('USERENV', 'CURRENT_USER');
  9    l_output := replace(replace(l_user2, 'DINH', ''),'PD', '');
 10    dbms_output.put_line (l_user);
 11    dbms_output.put_line (l_output);
 12  END;
 13  /
099
099

PL/SQL procedure successfully completed.

DINH099PD@ORCLPDB1 >

ACFS Mystery

From ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

ACFS and AFD 12.2.0.1 Supported Platforms
Vendor          :  RedHat Linux 
Version         : 7 
Update / Kernel : Update 6 3.10.0-957 and later / 3.10.0 Red Hat kernels X86_64	
ACFS Bug or PSU	: 12.2.0.1.191015 (Base Bug 29963428)

Where: “Base” (at the “Bug or PSU” column) stands for the "12.2.0.1 Grid Infrastructure" release

Check current environment:

$ $ORACLE_HOME/OPatch/opatch lspatches
26928563;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:170711) (26928563)
26925644;OCW RELEASE UPDATE 12.2.0.1.0(ID:171003) (26925644)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)
26710464;Database Release Update : 12.2.0.1.171017 (26710464)
26247490;ACFS Interim patch for 26247490

OPatch succeeded.

$ $ORACLE_HOME/OPatch/opatch lsinventory|grep 29963428
$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed|grep 29963428

$ uname -r
3.10.0-1062.12.1.el7.x86_64

ACFS should not be supported since 29963428 has not been applied, but  it is.

# rpm -q kernel --last
kernel-3.10.0-1062.12.1.el7.x86_64            Mon 11 May 2020 14:39:40
kernel-3.10.0-957.5.1.el7.x86_64              Thu 31 Oct 2019 16:26:21
kernel-3.10.0-862.2.3.el7.x86_64              Mon 28 May 2018 11:27:22

# lsmod | grep oracle
oracleacfs 4626640 2
oracleadvm 776776 8
oracleoks 662840 2 oracleacfs,oracleadvm

# modinfo oracleoks
filename: /lib/modules/3.10.0-1062.12.1.el7.x86_64/weak-updates/usm/oracleoks.ko
author: Oracle Corporation
license: Proprietary
rhelversion: 7.4
srcversion: C5110F596402987AF02F894
depends:
vermagic: 3.10.0-693.el7.x86_64 SMP mod_unload modversions
signer: Oracle Linux RHCK Module Signing Key
sig_key: DD:99:5B:15:5C:19:B3:A7:C3:EF:77:07:B9:69:E2:5F:96:39:66:6E
sig_hashalgo: sha256

# acfsdriverstate version
ACFS-9325: Driver OS kernel version = 3.10.0-693.el7.x86_64(x86_64).
ACFS-9326: Driver Oracle version = RELEASE.
ACFS-9212: Driver build version = 12.2.0.1 (ACFSRU)..

# acfsdriverstate installed
ACFS-9203: true

# acfsdriverstate supported
ACFS-9200: Supported

# acfsroot version_check
ACFS-9316: Valid ADVM/ACFS distribution media detected at: '/a01/app/grid/12.2.0/usm/install/Oracle/EL7/x86_64/3.10.0-693/3.10.0-693-x86_64/bin'

# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.2.0.1.0]

# acfsutil registry
Mount Object:
  Device: /dev/asm/shared-57
  Mount Point: /shared
  Disk Group: shared
  Volume: shared
  Options: none
  Nodes: all
  Accelerator Volumes:

# acfsutil info fs
/shared
    ACFS Version: 12.2.0.1.0
    on-disk version:       46.0
    compatible.advm:       12.2.0.0.0
    ACFS compatibility:    12.2.0.0.0
    flags:        MountPoint,Available
    mount time:   Thu May 14 23:16:59 2020
    mount sequence number: 0
    allocation unit:       4096
    metadata block size:   4096
    volumes:      1
    total size:   987842478080  ( 920.00 GB )
    total free:   849604771840  ( 791.26 GB )
    file entry table allocation: 42336256
    primary volume: /dev/asm/shared-57
        label:
        state:                 Available
        major, minor:          252, 29185
        logical sector size:   512
        size:                  987842478080  ( 920.00 GB )
        free:                  849604771840  ( 791.26 GB )
        metadata read I/O count:         80140
        metadata write I/O count:        269
        total metadata bytes read:       328335360  ( 313.12 MB )
        total metadata bytes written:    1187840  (   1.13 MB )
        ADVM diskgroup:        shared
        ADVM resize increment: 536870912
        ADVM redundancy:       unprotected
        ADVM stripe columns:   8
        ADVM stripe width:     1048576
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
    replication status: DISABLED
    compression status: DISABLED

$ crsctl stat res -t -w "TYPE = ora.volume.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.advm
               ONLINE  ONLINE       xxxxxxx-01               STABLE
               ONLINE  ONLINE       xxxxxxx-02               STABLE
--------------------------------------------------------------------------------

$ crsctl stat res -t -w "TYPE = ora.acfs.type"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.shared.shared.acfs
               ONLINE  ONLINE       lp-ora1-rh               mounted on /shared,STABLE
               ONLINE  ONLINE       lp-ora2-rh               mounted on /shared,STABLE
--------------------------------------------------------------------------------

Do you see what’s wrong and why ACFS is supported?

ORA-14758: Last partition in the range section cannot be dropped

Quick and dirty post.

Works for 12.2.0.1 and failed with ORA-14758 below 12.2.0.1

00:51:45 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P661        YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10 rows selected.

00:51:49 DINH @ HAWK:HAWK:>alter table test drop partition D1;

Table altered.

00:52:00 DINH @ HAWK:HAWK:>alter table test drop partition D2;

Table altered.

00:52:08 DINH @ HAWK:HAWK:>alter table test drop partition D3;

Table altered.

00:52:20 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P662        YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P663        YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P664        YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

00:52:23 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P662;

Table altered.

00:52:46 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P663;

Table altered.

00:52:58 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P664;

Table altered.

00:53:09 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P665        YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P666        YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:53:12 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P665;

Table altered.

00:53:43 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P666;

Table altered.

00:53:53 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P667        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:04 DINH @ HAWK:HAWK:>alter table test drop partition SYS_P667;

Table altered.

00:54:22 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:28 DINH @ HAWK:HAWK:>insert into test values(to_date('01.01.2009', 'dd.mm.yyyy'), 1);

1 row created.

00:54:36 DINH @ HAWK:HAWK:>commit;

Commit complete.

00:54:41 DINH @ HAWK:HAWK:>@p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- --------------------------------------------------------------------------------
DINH         TEST         SYS_P661        NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DINH         TEST         SYS_P668        YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

00:54:44 DINH @ HAWK:HAWK:>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

====================================================================================================

18:59:19 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INT HIGH_VALUE
------------ ------------ --------------- --- -------------------------------------------------------------------------------------
MDINH        TEST         D1              NO  TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D2              NO  TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         D3              NO  TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P68         YES TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P69         YES TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P70         YES TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P71         YES TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P72         YES TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P73         YES TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MDINH        TEST         SYS_P74         YES TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

10 rows selected.

18:59:40 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

18:59:50 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

18:59:58 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


19:00:06 DB-FS-1:(MDINH@hawk):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db-fs-1 sql]$

SOLUTION:

I have been discussing this with LDC and agree the better solution is to TRUNCATE the partition.

How To Avoid ORA-14758 in Interval Partitioned Table (Doc ID 1526571.1)
(iv) Temporarily disable the interval partition.

Here is one example of Temporarily disable the interval partition.
This has side effects as shown which has worked until one day it stopped working.
Test cases are simple and real world may not be as such.

12:44:18 DB-FS-1:(MDINH@hawk):PRIMARY> @p

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         D1              NO                             TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D2              NO                             TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         D3              NO                             TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P101        YES                            TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        YES                            TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        YES                            TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        YES                            TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        YES                            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        YES                            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        YES                            TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

10 rows selected.

12:44:20 DB-FS-1:(MDINH@hawk):PRIMARY> drop alter table test drop partition D1;
drop alter table test drop partition D1
     *
ERROR at line 1:
ORA-00950: invalid DROP option


12:44:48 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D1;

Table altered.

12:45:45 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D2;

Table altered.

12:45:57 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test drop partition D3;
alter table test drop partition D3
                                *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped


12:46:07 DB-FS-1:(MDINH@hawk):PRIMARY> alter table test truncate partition D3;

Table truncated.

12:46:31 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval ();

Table altered.

12:50:23 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST drop partition D3;

Table altered.

12:50:44 DB-FS-1:(MDINH@hawk):PRIMARY> alter table TEST set interval (numtoyminterval(1,'YEAR'));

Table altered.

12:50:53 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME  INTERVAL                       HIGH_VALUE
------------ ------------ --------------- ------------------------------ ----------------------------------------------------------
MDINH        TEST         SYS_P101        NO                             TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102        NO                             TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103        NO                             TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104        NO                             TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105        NO                             TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106        NO                             TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107        NO                             TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

7 rows selected.

12:50:58 DB-FS-1:(MDINH@hawk):PRIMARY> insert into test values(to_date('01.01.2010', 'dd.mm.yyyy'), 1);

1 row created.

12:52:15 DB-FS-1:(MDINH@hawk):PRIMARY> commit;

Commit complete.

12:54:36 DB-FS-1:(MDINH@hawk):PRIMARY> @p.sql

TABLE_OWNER  TABLE_NAME   PARTITION_NAME           POS INT HIGH_VALUE
------------ ------------ --------------- ------------ --- ----------------------------------------------------------
MDINH        TEST         SYS_P101                   1 NO  TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P102                   2 NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P103                   3 NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P104                   4 NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P105                   5 NO  TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P106                   6 NO  TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P107                   7 NO  TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
MDINH        TEST         SYS_P108                   8 YES TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

8 rows selected.

12:54:40 DB-FS-1:(MDINH@hawk):PRIMARY>

Simplify RMAN Restore With Meaningful Tag

Here is a simple demo for how to restore RMAN backup in case of failed migration using RMAN tag.

List backup from File System:

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/*MIGRATION*
-rw-r----- 1 oracle oinstall  12886016 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
-rw-r----- 1 oracle oinstall   1073152 Feb 18 21:56 /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
-rw-r----- 1 oracle oinstall 112263168 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
-rw-r----- 1 oracle oinstall 212926464 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
-rw-r----- 1 oracle oinstall   2946560 Feb 18 21:57 /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
-rw-r----- 1 oracle oinstall      3584 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
-rw-r----- 1 oracle oinstall    114688 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
-rw-r----- 1 oracle oinstall   1114112 Feb 18 21:57 /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
[oracle@db-fs-1 ~]$

List backup from RMAN:

[oracle@db-fs-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Feb 19 04:21:17 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3291419015)

RMAN> list backup summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of database summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION

RMAN> list backup of archivelog all summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
43      B  A  A DISK        2020-FEB-18 21:57:22 1       1       YES        MIGRATION
46      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
49      B  A  A DISK        2020-FEB-18 21:57:32 1       1       YES        MIGRATION

RMAN> list backup of controlfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
45      B  F  A DISK        2020-FEB-18 21:57:25 1       1       YES        MIGRATION
48      B  F  A DISK        2020-FEB-18 21:57:31 1       1       YES        MIGRATION
52      B  F  A DISK        2020-FEB-18 21:57:36 1       1       YES        MIGRATION

RMAN> list backup of spfile summary tag='MIGRATION';


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
44      B  F  A DISK        2020-FEB-18 21:57:23 1       1       YES        MIGRATION
50      B  F  A DISK        2020-FEB-18 21:57:34 1       1       YES        MIGRATION

RMAN> list backupset 42,49,50,52;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
42      Incr 0  203.05M    DISK        00:00:29     2020-FEB-18 21:57:17
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:56:48
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  1    0  Incr 1428959    2020-FEB-18 21:56:48              NO    /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
49      3.00K      DISK        00:00:00     2020-FEB-18 21:57:32
        BP Key: 49   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:32

  List of Archived Logs in backup set 49
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    3       1429002    2020-FEB-18 21:57:26 1429040    2020-FEB-18 21:57:32

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
50      Full    96.00K     DISK        00:00:00     2020-FEB-18 21:57:34
        BP Key: 50   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:33
  SPFILE Included: Modification time: 2020-FEB-18 21:51:45
  SPFILE db_unique_name: HAWK

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
52      Full    1.05M      DISK        00:00:01     2020-FEB-18 21:57:36
        BP Key: 52   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION
        Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
        Keep: BACKUP_LOGS        Until: 2020-AUG-18 21:57:35
  Control File Included: Ckp SCN: 1429047      Ckp time: 2020-FEB-18 21:57:35

RMAN>

You are probably wondering why BS 49 with Piece Name: /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 contains archivelog?

RMAN backup script:

[oracle@db-fs-1 ~]$ cat /u01/backup/backup_keep.rman
spool log to /u01/backup/rman_keep_backup_migration.log
connect target;
set echo on
show all;
run {
allocate channel c1 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c2 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
allocate channel c3 device type disk format '/u01/backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
backup as compressed backupset incremental level 0 filesperset 1 check logical database
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
backup as compressed backupset archivelog from time 'trunc(sysdate)'
filesperset 8
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
run {
allocate channel c1 device type disk format '/u01/backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
backup as compressed backupset current controlfile
keep until time 'ADD_MONTHS(SYSDATE,6)' TAG='MIGRATION';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION';
report schema;
exit
[oracle@db-fs-1 ~]$

RMAN backup log snippet:

allocated channel: c1
channel c1: SID=57 device type=DISK

Starting backup at 2020-FEB-18 21:57:30

backup will be obsolete on date 2020-AUG-18 21:57:30
archived logs required to recover from this backup will be backed up
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2020-FEB-18 21:57:31
channel c1: finished piece 1 at 2020-FEB-18 21:57:32
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
current log archived

backup will be obsolete on date 2020-AUG-18 21:57:32
archived logs required to recover from this backup will be backed up
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set

******* input archived log thread=1 sequence=3 RECID=30 STAMP=1032731852 *******

channel c1: starting piece 1 at 2020-FEB-18 21:57:32
channel c1: finished piece 1 at 2020-FEB-18 21:57:33
piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 tag=MIGRATION comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01

Restore backup from RMAN:

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     805306368 bytes

Fixed Size                     8625856 bytes
Variable Size                314573120 bytes
Database Buffers             478150656 bytes
Redo Buffers                   3956736 bytes

RMAN> restore controlfile from '/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54';

Starting restore at 2020-FEB-19 03:41:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/fra/HAWK/controlfile/o1_mf_h4r8xqh6_.ctl
Finished restore at 2020-FEB-19 03:41:38

RMAN> alter database mount;

RMAN> catalog start with '/u01/backup' noprompt;

RMAN> restore database preview summary from tag='MIGRATION';

Starting restore at 2020-FEB-19 03:43:05
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
42      B  0  A DISK        2020-FEB-18 21:57:17 1       1       YES        MIGRATION
41      B  0  A DISK        2020-FEB-18 21:57:11 1       1       YES        MIGRATION
39      B  0  A DISK        2020-FEB-18 21:56:52 1       1       YES        MIGRATION
40      B  0  A DISK        2020-FEB-18 21:56:56 1       1       YES        MIGRATION


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
47      B  A  A DISK        2020-FEB-18 21:57:27 1       1       YES        MIGRATION
recovery will be done up to SCN 1428959
Media recovery start SCN is 1428959
Recovery must be done beyond SCN 1428964 to clear datafile fuzziness
Finished restore at 2020-FEB-19 03:43:05

RMAN> restore database from tag='MIGRATION';
RMAN> recover database until scn 1428965;

Starting recover at 2020-FEB-19 03:44:45
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/19/2020 03:44:45
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       HAWK     3291419015       PARENT  1          2017-JAN-26 13:52:29
2       2       HAWK     3291419015       PARENT  1408558    2020-FEB-18 18:49:45
3       3       HAWK     3291419015       PARENT  1424305    2020-FEB-18 20:02:49
4       4       HAWK     3291419015       PARENT  1425161    2020-FEB-18 20:19:50
5       5       HAWK     3291419015       PARENT  1425162    2020-FEB-18 20:33:05
6       6       HAWK     3291419015       PARENT  1426203    2020-FEB-18 21:13:15
7       7       HAWK     3291419015       CURRENT 1428966    2020-FEB-18 22:05:54

RMAN> recover database until scn 1428967;

Starting recover at 2020-FEB-19 03:47:41
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
channel ORA_DISK_1: piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 tag=MIGRATION
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc thread=1 sequence=1
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4rx8c8b_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u02/oradata/HAWK/archivelog/2020_02_19/o1_mf_1_1_h4s8gfjc_.arc RECID=32 STAMP=1032752861
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-FEB-19 03:47:42

RMAN> alter database open resetlogs;

Statement processed

RMAN> report schema;

Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u02/oradata/HAWK/datafile/o1_mf_system_h4s874gt_.dbf
3    470      SYSAUX               NO      /u02/oradata/HAWK/datafile/o1_mf_sysaux_h4s86of7_.dbf
4    70       UNDOTBS1             YES     /u02/oradata/HAWK/datafile/o1_mf_undotbs1_h4s86kbl_.dbf
7    5        USERS                NO      /u02/oradata/HAWK/datafile/o1_mf_users_h4s86ncz_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u02/oradata/HAWK/datafile/o1_mf_temp_h4s8jc3n_.tmp

RMAN> delete force noprompt backup tag='MIGRATION';

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
39      39      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44
40      40      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45
41      41      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43
42      42      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42
43      43      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46
44      44      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47
45      45      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48
46      46      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50
47      47      1   1   AVAILABLE   DISK        /u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49
48      48      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51
49      49      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52
50      50      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53
52      52      1   1   AVAILABLE   DISK        /u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1cuosf50_1_1_MIGRATION_44 RECID=39 STAMP=1032731809
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1duosf58_1_1_MIGRATION_45 RECID=40 STAMP=1032731816
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1buosf50_1_1_MIGRATION_43 RECID=41 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1auosf50_1_1_MIGRATION_42 RECID=42 STAMP=1032731808
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1euosf61_1_1_MIGRATION_46 RECID=43 STAMP=1032731841
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1fuosf63_1_1_MIGRATION_47 RECID=44 STAMP=1032731843
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1guosf64_1_1_MIGRATION_48 RECID=45 STAMP=1032731845
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1iuosf67_1_1_MIGRATION_50 RECID=46 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/HAWK_3291419015_20200218_1huosf67_1_1_MIGRATION_49 RECID=47 STAMP=1032731847
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1juosf6a_1_1_MIGRATION_51 RECID=48 STAMP=1032731851
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1kuosf6c_1_1_MIGRATION_52 RECID=49 STAMP=1032731852
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1luosf6e_1_1_MIGRATION_53 RECID=50 STAMP=1032731854
deleted backup piece
backup piece handle=/u01/backup/CTL_HAWK_3291419015_20200218_1muosf6f_1_1_MIGRATION_54 RECID=52 STAMP=1032752561
Deleted 13 objects


RMAN> exit


Recovery Manager complete.

[oracle@db-fs-1 ~]$ ls -alrt /u01/backup/
total 28
drwxrwxr-x 6 oracle oinstall  4096 Feb 18 19:11 ..
-rw-r--r-- 1 oracle oinstall  1104 Feb 18 20:40 backup_keep.rman
-rw-r--r-- 1 oracle oinstall 12346 Feb 18 21:57 rman_keep_backup_migration.log
drwxr-xr-x 2 oracle oinstall  4096 Feb 19 04:28 .
[oracle@db-fs-1 ~]$

Just a crazy idea.
Keep the same backup tag for all backups until the next level 0.

Backup TAG for daily level 0 backup:

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b%d)"
2020Feb19
[oracle@db-fs-1 ~]$

Backup TAG for weekly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)_WK$(date +%U)"
2020Feb_WK07
[oracle@db-fs-1 ~]$

Backup TAG for monthly level 0 backup

[oracle@db-fs-1 ~]$ echo "$(date +%Y%b)"
2020Feb
[oracle@db-fs-1 ~]$

Tag: ARCH for archivelog backup and may not be useful.
LV=A means archivelog backup.

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
69      B  A  A DISK        2020-FEB-19 13:29:02 1       1       NO         ARCH
70      B  A  A DISK        2020-FEB-19 13:29:03 1       1       NO         ARCH
71      B  F  A DISK        2020-FEB-19 13:29:04 1       1       NO         TAG20200219T132904

RMAN>

In writing this post, I realized the my own backup script will need some improvements.

Check Cluster Resources Where Target != State

Current version.

[oracle@racnode-dc2-1 patch]$ cat /etc/oratab
#Backup file is  /u01/app/12.2.0.1/grid/srvm/admin/oratab.bak.racnode-dc2-1 line added by Agent
-MGMTDB:/u01/app/12.2.0.1/grid:N
hawk1:/u01/app/oracle/12.2.0.1/db1:N
+ASM1:/u01/app/12.2.0.1/grid:N          # line added by Agent
[oracle@racnode-dc2-1 patch]$

Kill database instance process.

[oracle@racnode-dc2-1 patch]$ ps -ef|grep pmon
oracle   13542     1  0 16:09 ?        00:00:00 asm_pmon_+ASM1
oracle   27663     1  0 16:39 ?        00:00:00 ora_pmon_hawk1
oracle   29401 18930  0 16:40 pts/0    00:00:00 grep --color=auto pmon
[oracle@racnode-dc2-1 patch]$
[oracle@racnode-dc2-1 patch]$ kill -9 27663
[oracle@racnode-dc2-1 patch]$

Check cluster resource – close but no cigar (false positive)

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '(TARGET != ONLINE) or (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
               OFFLINE OFFLINE      racnode-dc2-1            STABLE
               OFFLINE OFFLINE      racnode-dc2-2            STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      3        OFFLINE OFFLINE                               STABLE
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

Check cluster resource – BINGO!

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '(TARGET = ONLINE) and (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

Another example:

[oracle@racnode-dc2-1 ~]$ crsctl stat res -t -w '(TARGET = ONLINE) and (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
ora.DATA.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
ora.FRA.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 ~]$

Learned something here.

[oracle@racnode-dc2-1 ~]$ crsctl stat res -v -w 'TYPE = ora.database.type'
NAME=ora.hawk.db
TYPE=ora.database.type
LAST_SERVER=racnode-dc2-1
STATE=ONLINE on racnode-dc2-1
TARGET=ONLINE
CARDINALITY_ID=1
OXR_SECTION=0
RESTART_COUNT=0
***** FAILURE_COUNT=1
***** FAILURE_HISTORY=1564015051:racnode-dc2-1
ID=ora.hawk.db 1 1
INCARNATION=4
***** LAST_RESTART=07/25/2019 02:39:38
***** LAST_STATE_CHANGE=07/25/2019 02:39:51
STATE_DETAILS=Open,HOME=/u01/app/oracle/12.2.0.1/db1
INTERNAL_STATE=STABLE
TARGET_SERVER=racnode-dc2-1
RESOURCE_GROUP=
INSTANCE_COUNT=2

LAST_SERVER=racnode-dc2-2
STATE=ONLINE on racnode-dc2-2
TARGET=ONLINE
CARDINALITY_ID=2
OXR_SECTION=0
RESTART_COUNT=0
FAILURE_COUNT=0
FAILURE_HISTORY=
ID=ora.hawk.db 2 1
INCARNATION=1
LAST_RESTART=07/25/2019 02:21:45
LAST_STATE_CHANGE=07/25/2019 02:21:45
STATE_DETAILS=Open,HOME=/u01/app/oracle/12.2.0.1/db1
INTERNAL_STATE=STABLE
TARGET_SERVER=racnode-dc2-2
RESOURCE_GROUP=
INSTANCE_COUNT=2

[oracle@racnode-dc2-1 ~]$

Check cluster resource – sanity check.

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '(TARGET = ONLINE) and (STATE != ONLINE)'
[oracle@racnode-dc2-1 patch]$
[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w 'TYPE = ora.database.type'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  ONLINE       racnode-dc2-1            Open,HOME=/u01/app/o
                                                             racle/12.2.0.1/db1,S
                                                             TABLE
      2        ONLINE  ONLINE       racnode-dc2-2            Open,HOME=/u01/app/o
                                                             racle/12.2.0.1/db1,S
                                                             TABLE
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

Find Database Growth Using OEM Repository

Typically, what as been done is to schedule job for each database to collect database growth.

This may be problematic as it can be forgotten when new databases are created versus the likelihood of forgetting to add database to monitoring for OEM.

EM12c, EM13c : Querying the Repository Database for Building Reports using Metric Information (Doc ID 2347253.1)

Those raw data are inserted in various tables like EM_METRIC_VALUES for example. 
EM aggregates those management data by hour and by day. 
Those raw data are kept 7 days; the one hour aggregated data are kept 31 days, while one day aggregated data are kept one year.

How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

The above blog post provided a good starting point.

This post is using query to collect database size (metric_name=’DATABASE_SIZE’) vs tablespace size (metric_name=’tbspAllocation’) to avoid having to sum all tablespaces to determine database size.

OMS: 13.2.0 and EMREP DB: 12.2.0

Comparison for METRIC_COLUMN between DATABASE_SIZE and tbspAllocation.

For tbspAllocation, the size was not clear and did not research further but it does appear to be GB.

SQL> select distinct metric_name, METRIC_COLUMN from sysman.mgmt$metric_daily where metric_name='tbspAllocation' order by 1;

METRIC_NAME                                                      METRIC_COLUMN
---------------------------------------------------------------- ----------------------------------------------------------------
tbspAllocation                                                   spaceUsed
tbspAllocation                                                   spaceAllocated

SQL> select distinct METRIC_COLUMN from sysman.mgmt$metric_daily WHERE metric_name='DATABASE_SIZE';

METRIC_COLUMN
----------------------------------------------------------------
ALLOCATED_GB
USED_GB

TARGET_TYPE used (not all results presented):

SQL> select distinct target_type from sysman.mgmt$metric_daily order by 1;

TARGET_TYPE
----------------------------------------------------------------
oracle_database
oracle_pdb
rac_database

METRIC_NAME used (not all results presented):

SQL> select distinct metric_name from sysman.mgmt$metric_daily order by 1;

METRIC_NAME
----------------------------------------------------------------
DATABASE_SIZE
tbspAllocation

DEMO:

SQL> @dbsize.sql
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: perf

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxxperf                                           rac_database    01-MAR-18  2698.6       3526.8
                                                   rac_database    01-APR-18  2709.9       3526.8         2698.6        11.31
                                                   rac_database    01-MAY-18  2728.8       3526.8         2709.9        18.86
                                                   rac_database    01-JUN-18  2735.4       3548.8         2728.8         6.61
                                                   rac_database    01-JUL-18  2746.4       3548.8         2735.4        11.01
                                                   rac_database    01-AUG-18  2758.7       3548.8         2746.4        12.27
                                                   rac_database    01-SEP-18  2772.5       3548.8         2758.7        13.82
                                                   rac_database    01-OCT-18  4888.8       6207.8         2772.5       2116.3
                                                   rac_database    01-NOV-18  4647.8       6207.8         4888.8         -241
                                                   rac_database    01-DEC-18  3383.2       6207.8         4647.8        -1265
yyyyperf                                           oracle_database 01-MAR-18   63.07       395.58
                                                   oracle_database 01-APR-18   63.19       395.58          63.07          .12
                                                   oracle_database 01-MAY-18   64.33       395.58          63.19         1.14
                                                   oracle_database 01-JUN-18   64.81       395.58          64.33          .48
                                                   oracle_database 01-JUL-18    65.1       395.58          64.81          .29
                                                   oracle_database 01-AUG-18   65.22       395.58           65.1          .12
                                                   oracle_database 01-SEP-18   65.79       395.58          65.22          .57
                                                   oracle_database 01-OCT-18   68.18       395.58          65.79         2.39
                                                   oracle_database 01-NOV-18   75.79       395.72          68.18         7.61
                                                   oracle_database 01-DEC-18    80.4       395.72          75.79         4.61

29 rows selected.

SQL> @dbsize
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: *

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_CDBROOT         oracle_pdb      01-MAR-18    7.96        94.73
                                                   oracle_pdb      01-APR-18    3.44        94.73           7.96        -4.52
                                                   oracle_pdb      01-MAY-18   12.26        95.07           3.44         8.82
                                                   oracle_pdb      01-JUN-18   76.18        95.12          12.26        63.92
                                                   oracle_pdb      01-JUL-18   70.87        95.15          76.18        -5.31
                                                   oracle_pdb      01-AUG-18   77.63        95.15          70.87         6.76
                                                   oracle_pdb      01-SEP-18     4.9        95.15          77.63       -72.73
                                                   oracle_pdb      01-OCT-18       4        95.15            4.9          -.9
                                                   oracle_pdb      01-NOV-18   41.34        95.15              4        37.34
                                                   oracle_pdb      01-DEC-18   33.52        95.15          41.34        -7.82
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_xxxxxPDB        oracle_pdb      01-MAR-18  1610.6         2571
                                                   oracle_pdb      01-APR-18  1644.9         2571         1610.6        34.27
                                                   oracle_pdb      01-MAY-18  1659.3       2571.3         1644.9        14.43
                                                   oracle_pdb      01-JUN-18  1694.7       2571.4         1659.3        35.32
                                                   oracle_pdb      01-JUL-18  1753.8       2571.4         1694.7        59.18
                                                   oracle_pdb      01-AUG-18  1827.9       2571.4         1753.8        74.06
                                                   oracle_pdb      01-SEP-18  1900.8       2571.4         1827.9        72.91
                                                   oracle_pdb      01-OCT-18  1977.2       2571.4         1900.8        76.43
                                                   oracle_pdb      01-NOV-18  2044.8       2571.4         1977.2         67.6
                                                   oracle_pdb      01-DEC-18  2144.5       2571.4         2044.8        99.64

Script:

set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
undefine 1
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
SELECT target_name, target_type, trunc(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&&1','i')
GROUP BY target_name, target_type, trunc(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;


UPDATED SQL SCRIPT:

SQL> @dbsize.sql xxxprod
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxprod                                            rac_database    31-MAR-18   333.2       704.42
                                                   rac_database    30-APR-18  336.65       704.42          333.2         3.45
                                                   rac_database    31-MAY-18  350.48       704.42         336.65        13.83
                                                   rac_database    30-JUN-18  423.47        714.1         350.48        72.99
                                                   rac_database    31-JUL-18  397.42        714.1         423.47       -26.05
                                                   rac_database    31-AUG-18  415.61        714.1         397.42        18.19
                                                   rac_database    30-SEP-18   417.2       714.69         415.61         1.59
                                                   rac_database    31-OCT-18  421.04       714.69          417.2         3.84
                                                   rac_database    30-NOV-18  425.35       715.37         421.04         4.31
                                                   rac_database    20-DEC-18  428.44       723.11         425.35         3.09

10 rows selected.

SQL> !cat dbsize.sql



SQL> !cat dbsize.sql
set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
-- This shows LATEST date of month
SELECT target_name, target_type, MAX(rollup_timestamp) month_dt, metric_column, MAX(maximum) maximum
-- This shows FIRST date of month
-- SELECT target_name, target_type, TRUNC(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&1','i')
GROUP BY target_name, target_type, TRUNC(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
-- ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;
undefine 1


ReCreate ASM Disks RAC

A long time ago I had written about ReCreate ASM Disks; however this was single instance.

Basically, need to duplicate 11.2.0.4 database to 12.2 RAC. Unfortunately, compatible.rdbms=12.1.0.2.0.

alter diskgroup DATA set attribute ‘compatible.rdbms’ = ‘11.2’ does not work.

You are thinking, “Why not create with 11.2 to start with?”

I am using oravirt (Mikael Sandström) · GitHub to build RAC environment.

This is the best, hands down resource to build sand box and unfortunately for me, have not figured out how to configured all the details.

NOTE: This is a newly created environment and does not contain any database.

15:51:36 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
           1 CRS                            12.2.0.1.0                                                   11.2.0.0.0
           2 DATA                           12.2.0.1.0                                                   12.1.0.2.0
           3 FRA                            12.2.0.1.0                                                   12.1.0.2.0

15:51:44 SYS @ +ASM1:>alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2';
alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.rdbms
ORA-15244: new compatibility setting less than current [12.1.0.2.0]


15:51:52 SYS @ +ASM1:>

+++ Review DG attributes
[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G DATA
Name                        Value
access_control.enabled      FALSE
access_control.umask        066
appliance._partnering_type  GENERIC
au_size                     4194304
cell.smart_scan_capable     FALSE
cell.sparse_dg              allnonsparse
compatible.advm             12.2.0.1.0
compatible.asm              12.2.0.1.0
compatible.rdbms            12.1.0.2.0
content.check               FALSE
content.type                data
disk_repair_time            3.6h
failgroup_repair_time       24.0h
idp.boundary                auto
idp.type                    dynamic
logical_sector_size         512
phys_meta_replicated        true
preferred_read.enabled      FALSE
scrub_async_limit           1
scrub_metadata.enabled      FALSE
sector_size                 512
thin_provisioned            FALSE

[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G FRA
Name                        Value
access_control.enabled      FALSE
access_control.umask        066
appliance._partnering_type  GENERIC
au_size                     4194304
cell.smart_scan_capable     FALSE
cell.sparse_dg              allnonsparse
compatible.advm             12.2.0.1.0
compatible.asm              12.2.0.1.0
compatible.rdbms            12.1.0.2.0
content.check               FALSE
content.type                data
disk_repair_time            3.6h
failgroup_repair_time       24.0h
idp.boundary                auto
idp.type                    dynamic
logical_sector_size         512
phys_meta_replicated        true
preferred_read.enabled      FALSE
scrub_async_limit           1
scrub_metadata.enabled      FALSE
sector_size                 512
thin_provisioned            FALSE

+++ Review DG Path
[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G DATA
Path
ORCL:DATA01

[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G FRA
Path
ORCL:FRA01

+++ Remove DG from Cluster.
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup DATA -force
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup FRA -force

+++ Dismount DG before drop.
15:56:28 SYS @ +ASM1:>drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup DATA is mounted by another ASM instance

15:58:07 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup;


   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED
         1 CRS                            MOUNTED
         1 DATA                           MOUNTED
         1 FRA                            MOUNTED

6 rows selected.

15:58:37 SYS @ +ASM1:>alter diskgroup DATA dismount;

Diskgroup altered.

15:58:47 SYS @ +ASM1:>alter diskgroup FRA dismount;

Diskgroup altered.

15:59:02 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           DISMOUNTED
         1 FRA                            DISMOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

15:59:10 SYS @ +ASM1:>

+++ Drop DG from 2nd instance.
16:00:42 SYS @ +ASM2:>drop diskgroup DATA including contents;

Diskgroup dropped.

16:01:06 SYS @ +ASM2:>drop diskgroup FRA including contents;

Diskgroup dropped.

16:01:17 SYS @ +ASM2:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         2 CRS                            MOUNTED

16:01:27 SYS @ +ASM2:>

+++ Review ASM Disks.
[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm listdisks
CRS01
DATA01
FRA01

[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

+++ Create and mount DG.
16:18:32 SYS @ +ASM2:>create diskgroup FRA external redundancy disk 'ORCL:FRA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';

Diskgroup created.

16:19:07 SYS @ +ASM2:>create diskgroup DATA external redundancy disk 'ORCL:DATA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';

Diskgroup created.

16:19:31 SYS @ +ASM2:>

+++ Mount DG.
16:20:34 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           DISMOUNTED
         1 FRA                            DISMOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

16:20:37 SYS @ +ASM1:>alter diskgroup DATA mount;

Diskgroup altered.

16:21:01 SYS @ +ASM1:>alter diskgroup FRA mount;

Diskgroup altered.

16:21:10 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           MOUNTED
         1 FRA                            MOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

16:21:42 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
           1 CRS                            12.2.0.1.0                                                   11.2.0.0.0
           3 DATA                           12.2.0.1.0                                                   11.2.0.0.0
           2 FRA                            12.2.0.1.0                                                   11.2.0.0.0

16:21:45 SYS @ +ASM1:>

+++ Review ASM
[oracle@racnode-dc1-1 sql]$ srvctl status asm -v
ASM is running on racnode-dc1-1,racnode-dc1-2
Detailed state on node racnode-dc1-1: Started
Detailed state on node racnode-dc1-2: Started

[oracle@racnode-dc1-1 sql]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40952    40592                0           40592              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304      8188     8056                0            8056              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     12284    12152                0           12152              0             N  FRA/
[oracle@racnode-dc1-1 sql]$

Upgrade 12.2 Journey – DataGuard

Oracle Data Guard Broker Changes in Oracle Database 12c Release 2 (12.2.0.1)

How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)
Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby