RMAN-05609: Must specify a username for target connection when using active duplicate

In 11gR2 it was possible to run an active duplication command for data guard and cloning of DB via duplication without explicitly specifying a username for the target instance.
But in 12c1 this would lead to RMAN-05609: Must specify a username for target connection when using active duplicate error.

$ rman target / auxiliary sys/ent12c1db@ent12c1stns

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Aug 4 13:08:03 2014

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

connected to target database: ENT12C1 (DBID=209099011)
connected to auxiliary database: ENT12C1S (not mounted)

RMAN> duplicate target database for standby from active database
2> spfile
3> parameter_value_convert 'ent12c1','ent12c1s','ENT12C1','ENT12C1S'
4> set db_unique_name='ent12c1s'
5> set db_file_name_convert='/data/oradata/ENT12C1','/opt/app/oracle/oradata/ENT12C1S'
6> set log_file_name_convert='/data/oradata/ENT12C1','/opt/app/oracle/oradata/ENT12C1S','/data/flash_recovery/ENT12C1','/opt/app/oracle/fast_recovery_area/ENT12C1S'
7> set control_files='/opt/app/oracle/oradata/ENT12C1S','/opt/app/oracle/fast_recovery_area/ENT12C1S'
8> set db_create_file_dest='/opt/app/oracle/oradata'
9> set db_recovery_file_dest='/opt/app/oracle/fast_recovery_area'
10> set log_archive_max_processes='10'
11> set fal_client='ENT12C1STNS'
12> set fal_server='ENT12C1TNS'
13> set log_archive_dest_2='service=ENT12C1TNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent12c1'
14> set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent12c1s';

Starting Duplicate Db at 04-AUG-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/04/2014 13:08:11
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate

Solution is to include the username and password for the target instance as well.

$ rman target sys/ent12c1 auxiliary sys/ent12c1@ent12c1stns

This is different to how duplication was done on 11gR2 as such may require changes to duplication scripts when used with 12c.

ASM Disk Group Dependency Exists Even After Being Dropped

Database using ASM has storage has dependency on the ASM disk groups.

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

But it seems even if there's no real dependency exists between the database and ASM disk group, the ASM disk group is listed as a resource. By "no real dependency" it's meant that there's no database objects currently existing on disk group in concern. Following is the steps of the test case (tested on 11.2.0.3).
Create a new disk group and mount it on all nodes

SQL> create diskgroup test external redundancy disk '/dev/sdg1';

Diskgroup created.

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED
TEST                           MOUNTED
SQL> select name,state from v$asm_diskgroup;

As there are no database objects it's still not part of the DB configuration

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

But listed a resources

[oracle@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.TEST.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2

Dismount from all but one node and drop the disk group from the node it's mounted

SQL> alter diskgroup test dismount;
SQL> drop diskgroup test;
SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED

As seen from the above output disk group no longer exists and also is not listed on the resource list

[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2

This would be the expected behavior. Drop the disk group and it should be removed from the cluster. Next is the oddity.



Create the disk group as before and create some database objects. In this a tablespace is created

SQL> create diskgroup test external redundancy disk '/dev/sdg1';
SQL> create tablespace testtbs datafile '+test(datafile)' SIZE 10M;
SQL> ALTER USER ASANGA QUOTA UNLIMITED ON TESTTBS;

Creating the tablespace makes the disk group part of the configuration.

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

A table is created on the tablespace created earlier and few rows inserted to simulate some DB activity.

SQL> create table test (a number) tablespace testtbs;
SQL> insert into test values(10);
SQL> commit;
SQL> select * from test;

         A
----------
        10

Remove the database objects and drop the disk group

SQL> drop table test purge;
SQL> alter user asanga quota 0 on testtbs;
SQL> drop tablespace testtbs including contents and datafiles;

Even though there are no database objects on this disk group it is still part of the DB configuration

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

Dropping the disk group doesn't make any difference either

SQL> drop diskgroup test;

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
CLUSTER_DG                     MOUNTED
DATA                           MOUNTED
FLASH                          MOUNTED

[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.TEST.dg
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m2
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m2
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2


[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH,TEST
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

As seen from above outputs even though there's no disk group exists it's listed as part of the database configuration and listed as a resource. Trying to drop the resource result in following error.

[grid@rhel6m1 ~]$ crsctl delete resource ora.TEST.dg
CRS-2730: Resource 'ora.std11g2.db' depends on resource 'ora.TEST.dg'
CRS-4000: Command Delete failed, or completed with errors.

Solution is to remove database dependency on the disk group.

[oracle@rhel6m1 ~]$ srvctl modify database -d std11g2 -a "DATA,FLASH"

After which the disk group is not part of the DB configuration

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/STD11G2/PARAMETERFILE/spfile.257.806251953
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: std11g2
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,srv.domain.net
Type: RAC
Database is administrator managed

As there are no dependencies the delete command gets executed without any errors

[grid@rhel6m1 ~]$ crsctl delete resource ora.TEST.dg

Once deleted the disk group is no longer listed as a resource

[grid@rhel6m1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CLUSTER_DG.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.DATA.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.FLASH.dg
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.MYLISTENER.lsnr
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.asm
               ONLINE  ONLINE       rhel6m1                  Started
               ONLINE  ONLINE       rhel6m2                  Started
ora.gsd
               OFFLINE OFFLINE      rhel6m1
               OFFLINE OFFLINE      rhel6m2
ora.net1.network
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.ons
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
ora.registry.acfs
               ONLINE  ONLINE       rhel6m1
               ONLINE  ONLINE       rhel6m2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.MYLISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rhel6m1
ora.cvu
      1        ONLINE  ONLINE       rhel6m2
ora.oc4j
      1        ONLINE  ONLINE       rhel6m2
ora.rhel6m1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.rhel6m2.vip
      1        ONLINE  ONLINE       rhel6m2
ora.scan1.vip
      1        ONLINE  ONLINE       rhel6m1
ora.std11g2.db
      1        ONLINE  ONLINE       rhel6m1                  Open
      2        ONLINE  ONLINE       rhel6m2                  Open
ora.std11g2.myservice.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2
ora.std11g2.srv.domain.net.svc
      1        ONLINE  ONLINE       rhel6m1
      2        ONLINE  ONLINE       rhel6m2

Creating Extended Statistics With Function-base Column Groups

There are nine restrictions to creating extended statistics. These restrictions are same for version 11.1, 11.2 and 12.1.. One of the constraints is "A column group can not contain expressions". Oracle documentation also provides an example what is a column group and what is an expression when it comes to extended statistics extension "An example column group can be "(c1, c2)" and an example expression can be "(c1 + c2)". In short if columns are comma separated then it will consider as a column group. However this causes a problem when creating extended statistics with functions. Give below is an example.

SQL> create table exstat (a number, b date);

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

Trying to create extended statistics with column a and trunc(b) results in an error. What's clear from the error is that 1. Oracle was expecting a column group and 2. It must only contain columns separated by comma.
It is expecting a column group but the second portion of the extension is not recognized as a column hence the error. To overcome this create a function base index. For the above extended statistics extension following index was created

create index aidx on exstat(a,trunc(b));

After which the creation of the extended statistics works.

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'EXSTAT','(A,TRUNC(B))')
--------------------------------------------------------------------------------
SYS_STUE4B2X1G802ME0XHTBYWFY_Q

Simply create the index first and then extended statistics.



Trying to drop an extended statistics extension that uses function results in the following error

SQL>  exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))');
BEGIN dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))'); END;

*
ERROR at line 1:
ORA-20000: extension "(A,TRUNC(B))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13055
ORA-06512: at "SYS.DBMS_STATS", line 45105
ORA-06512: at line 1

Even though the extension is present the error message says "does not exist". To resolve this drop the index created for the extended statistics extension

SQL> DROP INDEX AIDX;

Index dropped.

After which extended statistics is dropped without any issue.

SQL> exec dbms_stats.drop_extended_stats(user,'EXSTAT','(A,TRUNC(B))');

PL/SQL procedure successfully completed.

This was tested on 12.1, 11.2.0.4 and 11.2.0.3 and all exhibited the same behavior. But this test failed on 11.1.0.7. even after creating the index.

SQL> create table exstat (a number, b date);

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

SQL> create index aidx on exstat(a,trunc(b));

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
       *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

The index has created a hidden virtual column. Trying to add a virtual column complains of duplication

SQL>  alter table exstat add (c as (trunc(b)));
 alter table exstat add (c as (trunc(b)))
                         *
ERROR at line 1:
ORA-54015: Duplicate column expression was specified

But having a virtual column with the function expression didn't help either.

SQL> drop index aidx;

SQL>  alter table exstat add (c as (trunc(b)));

SQL>  select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual;
 select dbms_stats.create_extended_stats(user,'EXSTAT','(a,trunc(b))') from dual
        *
ERROR at line 1:
ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

It's temping to use the virtual column itself in the extended statistics column group. But "extension cannot contain a virtual column" is restriction number one!

SQL> select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual;
select dbms_stats.create_extended_stats(user,'EXSTAT','(a,c)') from dual
       *
ERROR at line 1:
ORA-20001: Error when processing extension -  virtual column is referenced in a column expression