Tuesday, August 20, 2013

12C ADG Enhancements

  •          Explain plan now works on Standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> explain plan for select * from dual;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 272002086

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

  •          Data files can be moved in ADG mode. 

In 11G this could not be done and a shutdown of the standby was needed if you needed to move
datafiles as part of an ILM process if you were running an active dataguard configuration

In 11.2.0.3
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select thread#,sequence# from v$managed_standby where process like 'MRP%';

   THREAD#  SEQUENCE#
---------- ----------
         2       1652

SQL>  select tablespace_name from dba_tablespaces where status='READ ONLY';

TABLESPACE_NAME
------------------------------
TEST_2009Q1

SQL> select file_name from dba_data_files where tablespace_name='TEST_2009Q1';

FILE_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/FOOdr/datafile/TEST_2009Q1.510.dbf

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

[oracle@exadb03:FOODR1]/home/oracle => rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Aug 13 11:08:52 2013

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

connected to target database: FOO (DBID=2633655758)

RMAN> backup as copy tablespace TEST_2009Q1 format='+RECO';

Starting backup at 13-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4906 instance=FOODR1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00640 name=+DATA/FOOdr/datafile/TEST_2009Q1.510.dbf
output file name=+RECO/FOOdr/datafile/TEST_2009Q1.18424.823345739 tag=TAG20130813T110857 RECID=1539 STAMP=823345738
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-AUG-13

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>

RMAN> switch tablespace TEST_2009Q1 to copy;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 08/13/2013 11:11:17
RMAN-06572: database is open and datafile 640 is not offline

RMAN>


At this stage you need to shutdown the standby and restart it in mount mode and then run the switch to copy command.

This obviously impacts service if you have database services which run only on the standby and *DO NOT* run on the primary.

The 12C feature ALTER DATABASE MOVE DATAFILE solves this problem. Details from the documentation below