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

http://docs.oracle.com/cd/E16655_01/server.121/e17601/planned.htm#HAOVW11969

Looping synonyms and transportable

Whilst doing an export as part of TTS

> expdp directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 31 May, 2012 11:03:50

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv
ORA-39123: Data Pump transportable tablespace job aborted
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 902
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 838
ORA-01775: looping chain of synonyms

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:03:59


SYS > alter system set events '1775 trace name errorstack level 3';

System altered.

From the trace file generated

the failing SQL statement is

*** 2012-06-04 10:17:14.026
ksedmp: internal or fatal error
ORA-01775: looping chain of synonyms
Current SQL statement for this session:
SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
p.ts# AND u.user# = o.owner# AND u.name = x.owner
----- Call Stack Trace -----

So if you run the command manually you receive the same error.

SYS> SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
p.ts# AND u.user# = o.owner# AND u.name = x.owner 2
3 /
SELECT DISTINCT p.name, x.xmlschema, u.name FROM dba_xml_tables x, obj$ o, tab$ t, ts$ p, user$ u WHERE x.table_name = o.name AND o.obj# = t.obj# AND t.ts# =
*
ERROR at line 1:
ORA-01775: looping chain of synonyms



> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 31 11:04:03 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS> select object_name,owner,object_type from dba_objects where object_name='DBA_XML_TABLES';

OBJECT_NAME
--------------------------------------------------------------------------------
OWNER OBJECT_TYPE
------------------------------ -------------------
DBA_XML_TABLES
PUBLIC SYNONYM

So lets drop the public synonym

SYS > drop public SYNONYM DBA_XML_TABLES;

Synonym dropped.

SYS@>
and Real Application Testing options
> expdp directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 31 May, 2012 11:05:09

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=tmp_dir dumpfile=test_meta.dmp transport_tablespaces=tts_conv
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/tmp/test_meta.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:05:35

As you can see the export completes successfully

You can re-create the public synonym by running the below

catxdbv.sql:86:create or replace public synonym dba_xml_tables for dba_xml_tables;

If you do a search on MOS for the string "ORA-01775 dbms_tts" you find
Problem on DBMS_TTS ORA-01775: Looping Chain Of Synonyms [ID 1340262.1]

This note indicates the synonym can be dropped

Scheduler jobs and Active Standby

One of the kewl enhancements in 11g is fact that you can now have a standby database in read only active mode applying changes from the primary. You need to be licensed for the Active Dataguard feature but this entry is not about that..

I have been using Active Dataguard for more than 2 years where we have a application service which runs solely on the standby and is used by an adhoc query tool.Other than the odd bug it has proved to be quite useful

Now you would assume that everything in the dictionary on the primary (and not memory based views) would be visible on the standby.
So I was quite bemused when a simple query on dba_scheduler_jobs was not showing any information on the standby.
It turns out that the view definition as shown below

***********************************************************
AND j.class_oid = co.obj#(+)
AND ( j.database_role = v.database_role
OR (j.database_role IS NULL AND v.database_role = 'PRIMARY')

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

Hence when you query this view on the standby you do not see any jobs

RDBMS events

RDBMS events are often used to do additional tracing and for debug purposes.
Most of them are listed in $ORACLE_HOME/rdbms/mesg/oraus.msg
One such event I use quite often to determine which locks/enqueues a session is requesting is the following.
For example the below trace indicates that an innocuous looking query on v$flash_recovery_area_usage takes a controlfile lock in mode 4 which might not be the best thing to happen on a high throughput multi node RAC environment with a huge number of flashback logs.
SQL> alter session set events '10704 trace name context forever, level 10';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug
tracefile_name /u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_600.trc
SQL> select * from v$flash_recovery_area_usage;


*** 2010-07-30 10:07:33.978
ksqgtl *** CF-00000000-00000000 mode=4 flags=0x1a011 timeout=900 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
ksqlkdid: 0001-0036-00000169

*** 2010-07-30 10:07:33.978
*** ksudidTrace: ksqgtl
ksusesdi: 0001-0036-00000168
ksusetxn: 0001-0036-00000169

*** 2010-07-30 10:07:33.978
ksqcmi: CF,0,0 mode=4 timeout=900
ksqcmi: returns 0
ksqgtl: RETURNS 0

*** 2010-07-30 10:07:33.978
ksqgtl *** CF-00000000-00000004 mode=4 flags=0x10010 timeout=0 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
ksqlkdid: 0001-0036-00000169



Another event I have used in the past is related to parallel query to determine why PQ slaves do not get spawned
But to my surprise this event does not work anymore in 11.2

SQL> alter session set events '10392 trace name context forever, level 1';
Session altered.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_14748.trc

SQL> select /*+ parallel(a,8) */ count(*) from sys.obj$ a;

COUNT(*)
----------
231692

SQL> !cat /u01/app/oracle/diag/rdbms/TEST/TEST1/trace/TEST1_ora_14748.trc

*** 2010-07-30 14:41:02.547
*** SESSION ID:(316.58074) 2010-07-30 14:41:02.547
*** CLIENT ID:() 2010-07-30 14:41:02.547
*** SERVICE NAME:(SYS$USERS) 2010-07-30 14:41:02.547
*** MODULE NAME:(sqlplus@bart.au (TNS V1-V3)) 2010-07-30 14:41:02.547
*** ACTION NAME:() 2010-07-30 14:41:02.547

Processing Oradebug command 'setmypid'

*** 2010-07-30 14:41:02.547
Oradebug command 'setmypid' console output:

*** 2010-07-30 14:41:08.598
Processing Oradebug command 'tracefile_name'

*** 2010-07-30 14:41:08.598
Oradebug command 'tracefile_name' console output:

The trace does not contain any information

Feedback from Oracle was that “not many people use the px numeric ones and so they removed the code.”…
You can still use the _px_trace underscore parameter to determine why queries are not running in parallel

Dynamic Re-Mastering in 11g

This is a follow-up on a post from last year

http://el-caro.blogspot.com/search/label/10g%20RAC%20DRM

Node 1
========
SQL> create table obj as select * from sys.obj$;

Table created.

SQL> select object_id,owner,object_type from dba_objects where object_name='OBJ' and object_type='TABLE';

OBJECT_ID OWNER OBJECT_TYPE
---------- ------------------------------ -------------------
136388 REGOFA TABLE


SQL> select * from V$GCSPFMASTER_INFO where DATA_OBJECT_ID=136388;

no rows selected


Now we go to node 2 and manually master the object there...

Node 2
=========
SQL> oradebug setmypid
Statement processed.
SQL> oradebug lkdebug -m pkey 136388
Statement processed.
SQL> select * from V$GCSPFMASTER_INFO where data_object_id=136388;

FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER
---------- -------------- ----------- -------------- ---------------
REMASTER_CNT
------------
0 136388 Affinity 1 32767
1


Node 1
======

SQL> oradebug setmypid
Statement processed.
SQL> oradebug lkdebug -m pkey 136388
Statement processed.

Also Bug 5649377: REMASTER_CNT IN V$GCSPFMASTER_INFO IS NOT GETTING UPDATED
which I logged in 2006 is fixed in 11g

SQL> /

FILE_ID DATA_OBJECT_ID GC_MASTERIN CURRENT_MASTER PREVIOUS_MASTER
---------- -------------- ----------- -------------- ---------------
REMASTER_CNT
------------
0 136388 Affinity 0 1
2

As you can see the remaster_cnt is correctly set to 2 above
SQL> select * from X$KJDRMAFNSTATS;


ADDR INDX INST_ID DRMS AVG_DRM_TIME OBJECTS_PER_DRM
---------------- ---------- ---------- ---------- ------------ ---------------
QUIESCE_T FRZ_T CLEANUP_T REPLAY_T FIXWRITE_T SYNC_T RES_CLEANED
---------- ---------- ---------- ---------- ---------- ---------- -----------
REPLAY_S REPLAY_R MY_OBJECTS
---------- ---------- ----------
0000000060016FC0 0 1 104 11291 186
3219 28 1622 1302 4589 510 0
26 191959 2


SQL> select count(*) from V$GCSPFMASTER_INFO where current_master=0;

COUNT(*)
----------
2


DRM info is now also available in AWR tables via DBA_HIST_DYN_REMASTER_STATS

Also the following event can be used to trace DRM information

Event can be turned ON dynamically

SQL> alter system set events '10430 trace name context forever, level 10';

System altered.


*** 2010-04-07 21:02:23.831
Begin DRM(245) (swin 0) - AFFINITY transfer pkey 136388.0 to 2 oscan 1.1
kjiobjscn 1
ftd (30) received from node 1 (8/0.0.0)
all ftds received

* kjxftdn: break from kjxftdn, post lmon later

Event can be turned off
SQL> alter system set events '10430 trace name context off';

System altered.


Also the LMON and LMS trace files contains DRM related info

Although I have seen a lot of issues in 10G and turned off DRM in many cases I haven't seen any such issues
in 11.2 so far

Oracle Advanced Compression Advisor

Am currently working on how best to use compression techniques in an OLTP environment on a current 10g database which is due to be migrated to 11.2 in the next 6 months and I came across this OTN note

http://www.oracle.com/technology/products/database/compression/compression-advisor.html

The following statement got me very interested

This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1.
A compression advisor (DBMS_COMPRESSION) comes packaged with Oracle Database 11g Release 2.

So lets give it a try...

I downloaded the package and compiled it..

SQL> @dbmscomp.sql

Package created.


Synonym created.


Grant succeeded.

No errors.

SQL> @prvtcomp.plb

Package body created.


Synonym created.


Grant succeeded.

No errors.


SQL> set serveroutput on

SQL> exec dbms_comp_advisor.getratio('OWNER1','CROSS','OLTP',1);

Invalid Compression option for the current COMPAT setting


Thankfully the package body is not encrypted and a quick look at the code reveals that it does the following

SQL> create table DBMS_COMPRESSION_TEMP_UNCMP as select /*+ full('CROSS') */ * from cross sample block( 1);

Table created.

SQL> create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP;
create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP
*
ERROR at line 1:
ORA-00922: missing or invalid option

Hmmm... not sure how they expect "compress for all operations" to work in a pre 11g database...

Interrupting Flashback Database

So a user called me up and said he was flashing back a database and he was not too happy with the amount of time it was taking to complete the operation and so he did a Ctrl-C...

When he tried to open the database this is what he saw..

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

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2033832 bytes
Variable Size 520099672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6348800 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database


SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

===>... Well he just turned off flashback..

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

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

Looking through the alert.log

Fri Sep 18 08:35:05 2009
flashback database to restore point TEST_AFTER
Fri Sep 18 08:35:05 2009
ORA-38757 signalled during: flashback database to restore point TEST_AFTER...
Fri Sep 18 12:00:48 2009

No errors in the RVWR process trace file

So I just dropped the restore point and voila

> drop restore point TEST_AFTER;

Restore point dropped.

> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

> alter database open;

Database altered.