- Explain plan now works on Standby
SQL> select database_role from v$database;
DATABASE_ROLE----------------
PHYSICAL STANDBY
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
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.
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
