Tuesday, August 19, 2014

Nologging vs Logging for LOBs and enq: CF - contention

Changing the logging option is one of the possible performance tuning tasks when dealing with LOBs. However use of nologging will make recovery of these lob segments impossible as such use of this will also depends on the nature of the application data. For transient data where recovery is not expected in case of database failure nologging option would be suitable. When the logging option is not explicitly mentioned this results in the tablespace's logging option being inherited by the lob segment. Refer Oracle documentation for more logging related information on basicfile and securefile.
This post presents the result of a simple test carried out comparing the nologging vs logging for both basicfile and securefile LOB segments. The java code used for the test is given at the end of the post. First case is the basicfile. Nologging is not possible if the cache is enable on the lob segment. Therefore nocache option is chosen for both logging and nologging. The table definition is given below.
create table ses(sesid varchar2(100), sesob blob) SEGMENT CREATION IMMEDIATE TABLESPACE users
 LOB
  (
    sesob
  )
  STORE AS object_lob_seg (
  TABLESPACE lob32ktbs
  DISABLE STORAGE IN ROW
        CHUNK 32K
        NOCACHE NOLOGGING
        --NOCACHE LOGGING
        PCTVERSION 0
        STORAGE (MAXEXTENTS UNLIMITED)
        INDEX object_lob_idx (
            TABLESPACE lob32ktbs
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
/
The test involves inserting an 800KB LOB into the table 100 times and then later updating it with similar size LOB. Since the LOB is greater than 4K, the table is created with disable in row. Chunk size is set to 32k and the lob segment is stored in a tablespace of 32K block size (lob32ktbs) while the table resides in a different (users) tablespace of 8K block size. Pctversion is set to 0 as no consistent reads are expected. Only option that is changed is the logging option. The test database version is 11.2.0.3
Redo size statistic and the log file sync wait event times are compared for the two test runs as these are directly related to the logging option. Graphs below show the comparison of these for each test run.

There's no surprise that nologging option generates the lowest amount of redo. The logging test case generated around 83-84MB of redo for insert and update which is roughly the same size as the LOB inserted/updated (800KB x 100). There's minimal logging during the nologging test. Since redo is counted for the entire test, the redo seen could be the redo generated for the table data insert (as oppose to lobs) which still is on a tablespace with logging. Nevertheless a clear difference could be observed in the amount of redo generated when nologging is used. This is also reflected in the log file sync time for the two test cases which got reduced from several minutes to under a minute.
Next the same test was executed but this time the lob segment was stored as securefile. The table DDL is given below. Only difference apart from the securefile is Pctversion 0 has been replaced with retention none. All other settings are same as the basic file (tablespaces, database and etc). Chunk size is depreciated in securefile, and when specified is considered only as a guidance.
create table ses(sesid varchar2(100), sesob blob) SEGMENT CREATION IMMEDIATE TABLESPACE users
 LOB
  (
    sesob
  )
  STORE AS securefile object_lob_seg (
  TABLESPACE lob32ktbs
  DISABLE STORAGE IN ROW
        CHUNK 32K
        NOCACHE NOLOGGING
        --NOCACHE LOGGING
        RETENTION NONE
        STORAGE (MAXEXTENTS UNLIMITED)
        INDEX object_lob_idx (
            TABLESPACE lob32ktbs
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
/
Similar to the earlier test the nologging generated low amount of redo compared to logging and resulted in short wait on log file sync event.

Comparing redo size and log file sync time for nologging option between the basicfile and securefile shows a mix bag of results. Basicfile has performed well for inserts in reducing redo generated and log file sync while securefile performed well for updates.

Comparing the IO types on OEM console during the nologging test it was noted that securefile uses predominantly large writes while the basicfile uses small writes.
Comparing the test result with logging enabled the securefile out performance basic file for insert and updates in terms of log file sync wait time. Both securefile and basicfile generates roughly the same amount of redo. It must be noted nocache logging is the default for secure file.
The table below shows all the test results.




It seems that when application permits it's best to use nologging for lobs which reduce the amount of redo generated and log file sync waits. However there are some drawbacks to using nologging on LOBs which only comes to light when there are multiple sessions doing LOB related DMLS. Following is an AWR snippet from a load test on pre-production system.
After CPU the top wait event is log file sync and high portion of this wait event is due an basicfile LOB related insert statement that store some transient data. Changing the lob segment option to nologging resulted in lower log file sync time but it also introduced high enq: CF - contention wait times.
According to 1072417.1 enq: CF - contention is normal and expected when doing DML on LOBs with nocache nologging. CF contention occurs as oracle records the unrecoverable system change number (SCN) in the control file. From the application perspective the overall response time degraded after changing to nologging and had to be reverted back to cache logging.

Useful metalink notes
Performance Degradation as a Result of 'enq: CF - contention' [ID 1072417.1]
LOB Performance Guideline [ID 268476.1]
LOBS - Storage, Redo and Performance Issues [ID 66431.1]
LOBS - Storage, Read-consistency and Rollback [ID 162345.1]
Master Note - RDBMS Large Objects (LOBs) [ID 1268771.1]
Performance problems on a table that has hundreds of columns including LOBs [ID 1292685.1]
POOR PERFORMANCE WITH LOB INSERTS [ID 978045.1]
Securefiles Performance Appears Slower Than Basicfile LOB [ID 1323933.1]


Java Code Used for Testing
public class LobLoggingTest {

    final String URL = "jdbc:oracle:thin:@192.168.0.66:1521:ent11g2";
    final String USERNAME = "asanga";
    final String PASSWORD = "asa";

    public static void main(String[] args) {

        LobLoggingTest test = new LobLoggingTest();
        //Insert test
        test.insertTest();

        System.out.println("\n\n************* end of insert test **************\n\n");

        //Update test
        test.updateTest();

    }

    public void insertTest() {
        try {

            OracleDataSource pool = new OracleDataSource();
            pool.setURL(URL);
            pool.setUser(USERNAME);
            pool.setPassword(PASSWORD);

            Connection con = pool.getConnection();
            con.setAutoCommit(false);

            long t1 = System.currentTimeMillis();

            LobStat.displayStats(con);
            LobStat.displayWaits(con);

            byte[] x = new byte[800 * 1024];
            x[1] = 10;
            x[798 * 1024] = 20;

            for (int i = 0; i < 100; i++) {

                OraclePreparedStatement pr = (OraclePreparedStatement) con.prepareStatement("insert into ses values(?,?)");

                String sesid = "abcdefghijklmnopqrstuvwxy" + Math.random();
                pr.setString(1, sesid);
                pr.setBytes(2, x);

                pr.execute();
                con.commit();
                pr.close();

            }

            long t2 = System.currentTimeMillis();

            LobStat.displayStats(con);
            LobStat.displayWaits(con);

            con.close();

            System.out.println("time taken " + (t2 - t1));

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

public void updateTest() {
        try {

            OracleDataSource pool = new OracleDataSource();
            pool.setURL(URL);
            pool.setUser(USERNAME);
            pool.setPassword(PASSWORD);

            Connection con = pool.getConnection();
            con.setAutoCommit(false);

            String[] sesids = new String[100];

            PreparedStatement pr1 = con.prepareStatement("select sesid from ses");
            ResultSet rs1 = pr1.executeQuery();
            int i = 0;
            while (rs1.next()) {

                sesids[i] = rs1.getString(1);
                i++;
            }
            rs1.close();
            pr1.close();
            con.close();

            con = pool.getConnection();
            LobStat.displayStats(con);
            LobStat.displayWaits(con);

            OraclePreparedStatement pr = (OraclePreparedStatement) con.prepareStatement("update ses set SESOB=? where sesid=?");

            byte[] xx = new byte[800 * 1024];
            xx[1] = 10;
            xx[798 * 1024] = 20;

            long t1 = System.currentTimeMillis();
            for (String x : sesids) {

                pr.setBytes(1, xx);
                pr.setString(2, x);
                pr.execute();
                con.commit();
            }

            long t2 = System.currentTimeMillis();
            System.out.println("time taken " + (t2 - t1));

            LobStat.displayStats(con);
            LobStat.displayWaits(con);

            pr.close();
            con.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}


public class LobStat {

    public static void displayStats(Connection con) {
        try {
            PreparedStatement pr = con.prepareStatement("select name,value from v$mystat,v$statname where v$mystat.statistic#=v$statname.statistic# "
                    + " and v$statname.name in ('CPU used when call started',"
                    + " 'CPU used by this session','db block gets','db block gets from cache','db block gets from cache (fastpath)',"
                    + " 'db block gets direct','consistent gets','consistent gets from cache','consistent gets from cache (fastpath)',"
                    + " 'consistent gets - examination','consistent gets direct','physical reads','physical reads direct',"
                    + " 'physical read IO requests','physical read bytes','consistent changes','redo size','redo writes',"
                    + " 'lob writes','lob writes unaligned','physical writes direct (lob)','physical writes','physical writes direct','physical writes from cache','physical writes direct temporary tablespace'"
                    + " ,'physical writes direct temporary tablespace','securefile direct read bytes','securefile direct write bytes','securefile direct read ops'"
                    + " ,'securefile direct write ops') order by 1");

            ResultSet rs = pr.executeQuery();


            while(rs.next()){

                System.out.println(rs.getString(1)+" : "+rs.getDouble(2));
            }

            rs.close();
            pr.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    public static void displayWaits(Connection con){
        try {
            PreparedStatement pr = con.prepareStatement("select event,total_waits,TIME_WAITED_MICRO from  V$SESSION_EVENT where sid=SYS_CONTEXT ('USERENV', 'SID') and event in ('log file sync','enq: CF - contention')");
            ResultSet rs = pr.executeQuery();

            System.out.println("event : total waits : time waited micro");
            while(rs.next()){

                System.out.println(rs.getString(1)+" : "+rs.getLong(2)+" : "+rs.getLong(3));

            }

            rs.close();
            pr.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }
}

Monday, August 18, 2014

enq: HW - contention and latch: enqueue hash chains

An application uses serialized java objects to stored in the database to overcome the application server failovers. The java objects are stored as BLOB. Application has been using the same session storing mechanism for number of years (7+ years) and gone through the upgrades of Oracle versions 10.2 -> 11.1 -> 11.2 without any performance regress. During the inception (with a 10.2) high wait times on enq: HW - contention were encountered which was remedied with the use of the event 44951 (refer 740075.1)
However during a test, involving large number of concurrent application sessions (x10 baseline) the test system encountered high enq: HW - contention even though event 44951 has been set with level 1024.

Following actions were tried in order to reducing the number of and time spent on HW - contention event.
1. Large initial extent size for the lob segment.
  STORE AS "LOB_SEG"
  (
    ...(INITIAL 5368709120 NEXT 134217728 ...
  );
2. Large value for next extent, ideally this should be greater than the (average size of the LOB inserted x number of concurrent inserts).
STORE AS "LOB_SEG"
  (
    ...INITIAL 5368709120 NEXT 134217728 ...
  );
3. Tablespace with uniform extent allocation and extent size is greater than the (average size of the LOB inserted x number of concurrent inserts).
4. Large chunk size for lob segment and tablespace block size equal to the chunk size. Lob segment was placed in a tablespace with a block size of 32K and chunk size 32K was used for the lob semgment.
  STORE AS SECUREFILE "LOB_SEG"
  (
    TABLESPACE "TBS32K" CHUNK 32768
  );
However these actions only slightly reduced the HW - contention wait events and the performance was not satisfactory. At this point it was decided to use the secure file for the lob segments as it was considered better in performance compared to basic file.



Investigating secure file related issues came across the following MOS note (1532311.1) which mentioned high waits on buffer busy waits and enq: TX - contention when there are frequent updates on secure file lobs. Solution for this was to increase the secure file concurrency estimate hidden parameter (_securefiles_concurrency_estimate). However instead of relying on a parameter that depends on the concurrency, the application was modified by replacing the statement sequence of (insert/update) with an (insert/delete/insert).
Running the same test as earlier resulted in high latch: enqueue hash chain waits.

This wait event was as a result of bug 13775960 which results high enqueue hash chains for concurrent inserts on secure files (refer 13775960.8). Luckily there's patch for the bug (13775960 which supersede the patch 13395403) and once applied the enqueue hash chain wait events were resolved.
Even though Oracle documents says that secure file out perform the basic files as in this case the secure file themselves pose some issues of their own which must be tested against.

Useful metalink notes
Bug 13775960 - "enqueue hash chains" latch contention for delete/insert Securefile workload [ID 13775960.8]
Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation [ID 1532311.1]
Bug 2530125 - Hang possible with "enqueue hash chains" latch held during deadlock detection [ID 2530125.8]
Bug 13395403 - "enqueue hash chains" latch contention on Securefile blob DMLs - superseded [ID 13395403.8]
'enq HW - contention' For Busy LOB Segment [ID 740075.1]
How To Analyze the Wait Statistic: 'enq: HW - contention' [ID 419348.1]

Thursday, August 14, 2014

Upgrade Oracle Database 12c1 from 12.1.0.1 to 12.1.0.2

This post list the steps of upgrading from 12.1.0.1 to 12.1.0.2 for single instance database in a data guard configuration (physical standby). The single instance databases are non-CDB. When upgrading databases in a data guard configuration the upgrade process is initiated on the standby site by upgrading the standby database software first. Upgrade of database software to 12.1.0.2 is done as an out of place upgrade (oppose to in-place upgrade). As such the database software could be installed in a different oracle home while the redo apply is taking place. Installing 12.1.0.2 database software is identical to that of 12.1.0.1 and there no new steps to be carried out.
Once the database software is installed copy the spfile , initfile, oracle password file, tnsnames.ora and listener.ora file from the 12.1.0.1 oracle home into the 12.1.0.2 oracle home. In this case the 12.1.0.1 oracle home is /opt/app/oracle/product/12.1.0/dbhome_1 and 12.1.0.2 oracle home is /opt/app/oracle/product/12.1.0/dbhome_2
echo $ORACLE_HOME
/opt/app/oracle/product/12.1.0/dbhome_1  ## 12.1.0.1 ORALCE HOME 
cd $ORACLE_HOME/dbs
$ cp spfileent12c1s.ora initent12c1s.ora orapwent12c1s ../../dbhome_2/dbs/
$ cd ../network/admin/
$ cp tnsnames.ora listener.ora ../../../dbhome_2/network/admin/
Open the listener.ora file that is in the new ORACLE_HOME/network/admin and edit the static listener entries to reflect the new oracle home path. These static listener entries were created as part of the data guard configuration.
SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = ent12c1)
                (SID_NAME = ent12c1)
                (ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
        )
)
Defer the redo transport on the primary until the standby is mounted using the new 12.1.0.2 oracle home.
SQL> alter system set log_archive_dest_state_2='defer';
and cancel the redo apply on the standby
SQL> alter database recover managed standby database cancel;
Modify the /etc/oratab to reflect the new oracle association with the standby instance
cat /etc/oratab
ent12c1s:/opt/app/oracle/product/12.1.0/dbhome_2:N
Stop the listener started out of the 12.1.0.1 home.
Modify the environment variables so that they are pointed to new 12.1.0.2 oracle home (ORACLE_HOME,PATH and etc). Start the listener using the 12.1.0.2 oracle home. Verify that listener is started from the new home
$ lsnrctl start

Starting /opt/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/app/oracle/product/12.1.0/dbhome_2/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/hpc5/listener/alert/log.xml
...
Services Summary...
Service "ent12c1s" has 1 instance(s).
  Instance "ent12c1s", status UNKNOWN, has 1 handler(s) for this service..
Mount the standby database using the 12.1.0.2 oracle home and start the redo apply
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
Verify from the alert log that database is started using 12.1.0.2 oracle home parameter file.
ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2
System name:    Linux
Node name:      hpc5.domain.net
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
Using parameter settings in server-side spfile /opt/app/oracle/product/12.1.0/dbhome_2/dbs/spfileent12c1s.ora
Enable the redo transport on the primary
alter system set log_archive_dest_state_2='enable'
This conclude the upgrade activity on the standby. The standby database instance will be upgrade once the redo generated during the primary database upgrade is transported and applied on to the standby.



Although the setup used on this post consists of a data guard configuration the primary site steps are valid for single instances without data guard configuration as well. The software upgrade on the primary is done as an out of place upgrade. Once the 12.1.0.2 software is installed run the preupgrade script from the new home.
cd /opt/app/oracle/product/12.1.0/dbhome_2/rdbms/admin
SQL> @preupgrd.sql


Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in ENT12C1...
***************************************************************************


      ************************************************************

                 ====>> ERRORS FOUND for ENT12C1 <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.


 1) Check Tag:    PURGE_RECYCLEBIN
    Check Summary: Check that recycle bin is empty prior to upgrade
    Fixup Summary:
     "The recycle bin will be purged."

            You MUST resolve the above error prior to upgrade

      ************************************************************

      ************************************************************

              ====>> PRE-UPGRADE RESULTS for ENT12C1 <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/postupgrade_fixups.sql

      ************************************************************

***************************************************************************
Pre-Upgrade Checks in ENT12C1 Completed.
***************************************************************************

***************************************************************************
As instructed on the output run the fixup sql on the primary database
@/opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/preupgrade_fixups.sql
Run pre upgrade sql again and check of errors and warnings. If there are any errors of warnings fix them before continuing with DBUA. To being the upgrade run DBUA from the 12.1.0.2 home.
Select the database upgrade option.

Select the source oracle home and the instance to upgrade.

The DBUA detects the data guard configuration is in place and prompt to sync the standby database. This is not an error but an information provided by the DBUA. There should not be any archive gaps prior to the primary upgrade.
The network configuration page did not detect the listener running out of the 12.1.0.1 home. Because of this when the upgrade finished the redo transport failed as there was no tnsnames.ora file in the 12.1.0.2 home and standby was unable to fetch the archive logs as the listener was running out of the old home. If during the upgrade no listener is detected similar to below, manually copy the listener.ora and tnsnames.ora files to the 12.1.0.2 home and edit the oracle home entry for static listener registration.
Take a backup before the upgrade or allow DBUA to take a backup as part of the upgrade process.
Summary and upgrade
Upgrade results

Once the upgrade is completed set the environment variables to reflect the new oracle home.
Also run the post upgrade script as suggested by the pre upgrade sql
@/opt/app/oracle/cfgtoollogs/ent12c1/preupgrade/postupgrade_fixups.sql
Verify listener is running out of the new home. If as mentioned earlier listener.ora and tnsnames.ora files are not moved to the new oracle home, move them manually to the 12.1.0.2 home. Once moved stop the listener and start it to run out of the new 12.1.0.2 oracle home.
To validate the data guard configuration is working carry out few log file switches and verify they are received at the standby and applied.
Once tested and satisfied with the upgrade, update the compatible initialization parameter to 12.1.0.2. It is not possible to downgrade the database once this has been set. In this case the standby was the first to be set compatible = 12.1.0.2 followed by the primary. Read data guard admin guide for exact steps.
This conclude the upgrade from 12.1.0.1 to 12.1.0.2 This is not an extensive "how to" guide but only the highlights. For complete information refer the oracle upgrade guide and the following metalink notes.

Useful metalink notes
Oracle 12cR1 Upgrade Companion [ID 1462240.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) [ID 1520299.1]
Actions For DST Updates When Upgrading To Or Applying The 12.1.0.2 Patchset [ID 1665676.1]

Related Posts
Upgrading RAC from 11.2.0.4 to 12.1.0.2 - Grid Infrastructure
Upgrading RAC from 12.1.0.1 to 12.1.0.2 - Grid Infrastructure
Upgrade from 11.1.0.7 to 11.2.0.4 (Clusterware, ASM & RAC)
Upgrading from 10.2.0.4 to 10.2.0.5 (Clusterware, RAC, ASM)
Upgrade from 10.2.0.5 to 11.2.0.3 (Clusterware, RAC, ASM)
Upgrade from 11.1.0.7 to 11.2.0.3 (Clusterware, ASM & RAC)
Upgrading from 11.1.0.7 to 11.2.0.3 with Transient Logical Standby
Upgrading from 11.2.0.1 to 11.2.0.3 with in-place upgrade for RAC
In-place upgrade from 11.2.0.2 to 11.2.0.3
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 2
Upgrading from 11gR2 (11.2.0.3) to 12c (12.1.0.1) Grid Infrastructure

Wednesday, August 6, 2014

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.

Friday, August 1, 2014

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