RMAN-05531: a mounted database cannot be duplicated while the datafiles are fuzzy

This error is observed while we are trying active database duplication from standby database while MRP process is still running and applying the changes.

RMAN-00571:====================================================
RMAN-00569:==========ERROR MESSAGE STACK FOLLOWS============
RMAN-00571:====================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2014 17:03:07
RMAN-05501: aborting duplication of target database
RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

Recovery Manager complete

Bug 11715084Active duplicate should work when connected to Standby as source DB 
[ID 11715084.8]

While MRP is running, the datafiles in the standby database get continuously updated and RMAN active database duplication doesn't currently support the standby to be cloned while the standby database is in mounted mode and the datafiles are being continuously updated.This problem is fixed in 11.2.0.4 and on wards releases.

WORKAROUND 

follow these steps
1.Open the standby database(the target) in read only mode.
2.Stop MRP process
3.Proceed with the active database duplication

SWITCH OVER TO DISASTER RECOVERY DATABASE

Oracle switchover may be needed as an IT procedure to check the validity of the DR,For maintenance purpose or any other reasons. The following procedure might help to switchover to the Disaster Recovery Site in any case. Since switchover is planned unlike failover, we need to do the following precaution actions first.

PRECAUTIONS !

• Make sure that the full database backup is taken for safety just in case anything goes wrong during the switch over process and validate the backup taken.
• Make sure the backup encompasses all important files besides the data files and the archive logs like backup of control file, parameter files, etc.

INFORMATION 

primary database name PROD

primary database instance name PROD1

standby database name PROD

standby database instance name PRODSBY

production machine name RAC1

standby machine name RACSBY
Steps that has to be followed in the process of Switching over to Standby database


 1. Verify that both the primary and standby parameter files support role transition.

$ export ORACLE_SID=PROD1

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 07:46:45 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
THE INSTANCE NAME IS  PROD1
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
---------       --------------------         ----------------
PROD        READ WRITE           PRIMARY
$ export ORACLE_SID=PRODSBY
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 07:46:45 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
THE INSTANCE NAME IS  PRODSBY
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
---------       --------------------         ----------------
PROD        READ WRITE           PHYSICAL STANDBY
2. Verify that there is network connectivity between the primary and standby
locations.
$ ping RAC1 from standby machine OS and should reply, alternatively ip can be used.
$ ping RACSBY from production machine OS again should reply

3. Each location in the Data Guard configuration should have connectivity through Oracle Net to the primary database and to all associated standby databases. This can be checked by
tnsping  PROD ..........from the standby
nslookup RAC1 ........from the standby
4. Convert the primary database to the new standby
 alter database commit to switchover to physical standby;
5. Shutdown the former primary and mount as a standby database:
shutdown immediate
startup nomount
alter database mount standby database;
6. Defer the remote archive destination on the old primary
alter system set log_archive_dest_state_2=defer;
7. Verify that the physical standby can be converted to the new primary
select switchover_status from v$database;
8. Convert the physical standby to the new primary
alter database commit to switchover to primary;
9. Shutdown and startup the new primary
Connect as ‘oracle’ user and’ PRODSBY’ ORACLE_SID
shutdown immediate
startup
10. Enable remote archiving on the new primary to the new standby
alter system set log_archive_dest_state_2=enable;
11. Start managed recover on the new standby database
recover managed standby database disconnect;
12. Check the gap between the new standby and new primary

TOP SEGMENTS IN ORACLE DATABASE BY SIZE

The following script lists out the top segments in your database by size grouping by segment type. 



COLUMN segment_type          FORMAT A20                HEADING 'Segment_Type'
COLUMN owner                        FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment_Name'
COLUMN partition_name         FORMAT A30                HEADING 'Partition_Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace_Name'
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

BREAK ON segment_type SKIP 1

COMPUTE sum OF bytes ON segment_type

SELECT
    a.segment_type      segment_type
  , a.owner             owner
  , a.segment_name      segment_name
  , a.partition_name    partition_name
  , a.tablespace_name   tablespace_name
  , a.bytes             bytes
  , a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

Segment_Type         Owner    Segment_Name     Partition_Name      Tablespace_Name     Bytes          Extents
-----------------               --------    ---------------------    ----------------------     -------------------------     --------           ----------
INDEX                         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOB PARTITION      xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOBSEGMENT         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE               xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE PARTITION xxx         xxxxxxxx           xxxxxxxx               xxxxxxxx                 xxxx               xxxxx

MONITORING TABLESPACE FOR GROWTH,USED and FREE SPACE

As a DBA one of the routine tasks we should do is keeping an eye on the growth of the tablespaces in our databases. It is also important to check which tablespace  has been using how much of the storage space allocated to it and how much is left for use out of it? The point here is to make sure that the database is not in danger of freezing from shortage of space as segments keep on increasing in size. I have posted the following scripts along with respective sample outputs. 

The following script is used to display the tablespace growth over a period of time

set linesize 175 
set pagesize 2000
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
 ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
 max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
 DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name 
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname, days;

DAYS       TSNAME                         CUR_SIZE_GB     USEDSIZE_GB
----------       -------------------            -----------                  -----------
01-11-2012 EMPDATA                        1119.87                1117.33
17-10-2014 EMPDATA                        6145.67                6074.91
18-10-2014 EMPDATA                        6145.67                6078.53
19-10-2014 EMPDATA                        6145.67                6078.54
20-10-2014 EMPDATA                        6145.67                6078.54
21-10-2014 EMPDATA                        6145.67                6078.94
22-10-2014 EMPDATA                        6145.67                6079.16
23-10-2014 EMPDATA                        6145.67                6080.6
24-10-2014 EMPDATA                        6145.67                6086.11
25-09-2014 EMPDATA                        6129.67                6000.02
25-10-2014 EMPDATA                        6145.67                6086.11
26-09-2014 EMPDATA                        6133.67                6043.16
27-09-2014 EMPDATA                        6133.67                6001.93
28-09-2014 EMPDATA                        6133.67                6007.77
29-09-2014 EMPDATA                        6133.67                6018.81

30-09-2014 EMPDATA                        6145.67                6073.52


The following script checks tablespaces for percentage of used and free spaces.


SET LINESIZE 175
SET PAGESIZE 5000
SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
 FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Tablespace                       Size (MB)            Free (MB)            % Free          % Used
------------------------------            ----------                ----------             - ---------       ----------
UNDOTBS1                         32767.9844         21782.875             66             34
TEMP                                  391                        197                       50             50
SYSAUX                             8228                    4052.25                 49             51
SYSTEM                             6530                    2898.1875             44             56

ACTIVE DATABASE DUPLICATION IN FEW STEPS

STEP 1. Prepare the parameter file

Sample Parameter file may look like this 

/home/oracle$cat TESTDB.ora

audit_file_dest='/u01/app/oracle/admin/TESTDB/adump'

audit_trail='DB'
compatible='11.2.0.0.0'
control_files= '+DG_GRP1/TESTDB/controlfile/control01.ctl','+DG_GRP2/TESTDB/controlfile/control01.ctl'
db_file_name_convert= '+ DATA/PROD/','+DG_GRP1/TESTDB'
log_file_name_convert= '+FRA/PROD/','+ DG_GRP2/TESTDB'
db_block_size=8192
db_create_file_dest='+DG_GRP1'
db_recovery_file_dest='+DG_GRP2'
db_domain=''
db_name='TESTDB' 
db_recovery_file_dest_size=4000G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDB)'
fast_start_mttr_target=0
filesystemio_options='asynch'
log_archive_format='%t_%s_%r.dbf'
log_buffer=268427264 # log buffer update
log_checkpoint_timeout=1800
memory_target=40G
open_cursors=300
optimizer_index_cost_adj=100
optimizer_mode='ALL_ROWS'
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=5000
query_rewrite_enabled='TRUE'
query_rewrite_integrity='enforced'
remote_login_passwordfile='EXCLUSIVE'
result_cache_max_size=367008K
session_cached_cursors=50
sessions=7560
skip_unusable_indexes=TRUE
undo_tablespace='UNDOTBS1'
processes=5000

STEP 2. Create the necessary directory

mkdir –p /u01/app/oracle/admin/TESTDB/adump

STEP 3. Modify the tnsnames.ora and listener.ora files
Insert the following entry in the tnsnames.ora file located in the $ORACLE_HOME/network/admin directory
TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )
Insert the following entry in the listener.ora file located in the $ORACLE_HOME/network/admin directory

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.8.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME=TESTDB)
      (GLOBAL_DBNAME=TESTDB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1 )
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON        # line added by Agent

STEP 4. Startup the auxiliary instance in nomount stage and test connectivity
export ORACLE_SID=TESTDB
sqlplus / as sysdba
SQL> startup nomount pfile=’/home/oracle/ TESTDB.ora’
ORACLE instance started.
Total System Global Area  252776600 bytes
Fixed Size       450712 bytes
Variable Size    218103808 bytes
Database Buffers    33554432 bytes
Redo Buffers       667648 bytes
SQL> 

$tnsping TESTDB

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 27-OCT-2014 16:19:02

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

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=TESTDB))(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.15)(PORT=1521)))
OK (0 msec)


$tnsing PROD

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 27-OCT-2014 16:19:02

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

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PROD))(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.8.15)(PORT=1521)))
OK (0 msec)

$rman target SYS/oracle@PROD auxiliary SYS/oracle@TESTDB
Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=839852638)
connected to auxiliary database: TESTDB (not mounted)
RMAN>

STEP 5. Prepare the duplication shell and rman scripts
5a. prepare the rman command
 $cat /u01/app/oracle/scripts/rman_active_dup_testdb.rcv
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate auxiliary channel t5 type disk;
allocate auxiliary channel t6 type disk;
allocate auxiliary channel t7 type disk;
allocate auxiliary channel t8 type disk;
duplicate target database to TESTDB from active database;
} 
5b) Prepare the shell script or executable and put it in $ORACLE_HOME/bin directory
$cat /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/TESTDBACTDUP
#!/bin/ksh
export ORACLE_HOME='/u01/app/oracle/product/11.2.0.3/dbhome_1'
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:/u01/app/oracle/product/11.2.0.3/dbhome_1/bin
export ORACLE_SID=TESTDB

rman target SYS/oracle@PROD auxiliary SYS/oracle@TESTDB log /u02/backup/rman/logs/testdbactive_dup.log CMDFILE='/u01/app/oracle/scripts/rman_active_dup_testdb.rcv'

STEP 6. Execute the scripts

$ksh -x TESTDBACTDUP

STEP 7. Monitor the progress by tailing the logfile

$ tail -1000f /u02/backup/rman/logs/testdbactive_dup.log