STEP BY STEP GUIDE FOR CREATION OF SINGLE NODE STANDBY FROM 2 NODE RAC

• CRETE A PASSWORD FILE FROM RAC NODE 1 AND COPY IT TO ORACLE_HOME/dbs DIRECTORY TO NODE2 AND DISASTER RECOVERY SITE

PROD1$ cd $ORACLE_HOME/dbs

PROD1$orapwd file=orapwCATDB1 entries=5 force=y
PROD1$scp orapwCATDB1 oracle@PROD2:$ORACLE_HOME/dbs

PROD1$scp orapwCATDB1 oracle@STDBY:$ORACLE_HOME/dbs

                          ON SECOND NODE

PROD2$cd $ORACLE_HOME/dbs

PROD2$mv orapwCATDB1 orapwCATDB2

                          ON STANDBY NODE

STDBY$cd $ORACLE_HOME/dbs

STDBY$mv orapwCATDB1 orapwCATDBSTDBY

• MODIFY THE tnsnames.ora (found in $ORACLE_HOME/network/admin directories in each server) FILE  TO CONTAIN THE FOLLOWING ENTRIES

CATDB1 =

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = PROD1)(PORT = 1521))

         (CONNECT_DATA =

            (SERVER = DEDICATED)

              (SERVICE_NAME = CATDB)

            (SID = CATDB1)

       )

  )

CATDB2 =    

     (DESCRIPTION =        

         (ADDRESS = (PROTOCOL = TCP)(HOST = PROD2)(PORT = 1521))

           (CONNECT_DATA = 

             (SERVER = DEDICATED)              

               (SERVICE_NAME = CATDB)           

            (SID = CATDB2)   

    )  

)

CATDBSTDBY =

    (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = STDBY)(PORT = 1521))

         (CONNECT_DATA =

            (SERVER = DEDICATED)

              (SERVICE_NAME = CATDBSTDBY)

       )

  )

 

• TAKE BACKUP OF THE PRODUCTION DATABASE.

PROD1$export ORACLE_SID=CATDB1

PROD1$rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Nov 18 11:12:24 2014

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

connected to target database: CATDB (not mounted)

RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
backup as compressed backupset database format '/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_%U';
backup as compressed backupset archivelog all format '/u03/backup/rman/CATDB/CATDB_Primary_archive_%U.arc';
backup current controlfile for standby format '/u03/backup/rman/CATDB/CATDB_standbycontrol.ctl';
}

• MOVE THE BACKUP TO THE DISASTER RECOVERY SITE

PROD1$cd /u03/backup/rman/CATDB

PROD1$ls

CATDB_Primary_databkp_standby_adpnto9u_1_1

CATDB_Primary_databkp_standby_aepnto9v_1_1
CATDB_Primary_databkp_standby_a8pnto9r_1_1
CATDB_Primary_databkp_standby_afpnto9v_1_1
CATDB_Primary_databkp_standby_a9pnto9s_1_1
CATDB_Primary_databkp_standby_agpntoa0_1_1
CATDB_Primary_databkp_standby_aapnto9s_1_1
CATDB_Primary_databkp_standby_abpnto9t_1_1
CATDB_Primary_databkp_standby_acpnto9t_1_1
CATDB_Primary_archive_ahpntof2_1_1.arc
CATDB_Primary_archive_ajpntof2_1_1.arc
CATDB_Primary_archive_aipntof2_1_1.arc
CATDB_Primary_archive_aopntof3_1_1.arc
CATDB_Primary_archive_ampntof3_1_1.arc
CATDB_Primary_archive_anpntof3_1_1.arc
CATDB_Primary_archive_alpntof3_1_1.arc
CATDB_Primary_archive_akpntof3_1_1.arc
CATDB_Primary_archive_appntof3_1_1.arc
CATDB_standbycontrol.ctl

PROD1$scp * oracle@STDBY:$PWD

• CHECK THAT ALL THE FILES ARRIVED ON THE INTENDED STANDBY SERVER

STDBY$cd /u03/backup/rman/CATDB

STDBY$ls
CATDB_Primary_databkp_standby_adpnto9u_1_1
CATDB_Primary_databkp_standby_aepnto9v_1_1
CATDB_Primary_databkp_standby_a8pnto9r_1_1
CATDB_Primary_databkp_standby_afpnto9v_1_1
CATDB_Primary_databkp_standby_a9pnto9s_1_1
CATDB_Primary_databkp_standby_agpntoa0_1_1
CATDB_Primary_databkp_standby_aapnto9s_1_1
CATDB_Primary_databkp_standby_abpnto9t_1_1
CATDB_Primary_databkp_standby_acpnto9t_1_1
CATDB_Primary_archive_ahpntof2_1_1.arc
CATDB_Primary_archive_ajpntof2_1_1.arc
CATDB_Primary_archive_aipntof2_1_1.arc
CATDB_Primary_archive_aopntof3_1_1.arc
CATDB_Primary_archive_ampntof3_1_1.arc
CATDB_Primary_archive_anpntof3_1_1.arc
CATDB_Primary_archive_alpntof3_1_1.arc
CATDB_Primary_archive_akpntof3_1_1.arc
CATDB_Primary_archive_appntof3_1_1.arc
CATDB_standbycontrol.ctl

• INITIATE THE RESTORE AND RECOVER PROCESS

STDBY$rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Nov 18 11:12:51 2014

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

connected to target database: CATDB (not mounted)

RMAN> set dbid=2344484010

executing command: SET DBID

RMAN> restore standby controlfile from '/u03/backup/rman/CATDB/CATDB_standbycontrol.ctl';

Starting restore at 11/18/14 11:14:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3907 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/catdbstdby/controlfile/control01.ctl
output file name=+ARCHIVE/catdbstdby/controlfile/control02.ctl
Finished restore at 11/18/14 11:14:24

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> allocate channel c4 type disk;
6> allocate channel c5 type disk;
7> allocate channel c6 type disk;
8> allocate channel c7 type disk;
9> allocate channel c8 type disk;
10> allocate channel c9 type disk;
11> allocate channel c10 type disk;
12> restore database;
13> }

allocated channel: c1
channel c1: SID=5167 device type=DISK

allocated channel: c2
channel c2: SID=5419 device type=DISK

allocated channel: c3
channel c3: SID=5545 device type=DISK

allocated channel: c4
channel c4: SID=5671 device type=DISK

allocated channel: c5
channel c5: SID=5797 device type=DISK

allocated channel: c6
channel c6: SID=5923 device type=DISK

allocated channel: c7
channel c7: SID=6049 device type=DISK

allocated channel: c8
channel c8: SID=6175 device type=DISK

allocated channel: c9
channel c9: SID=6301 device type=DISK

allocated channel: c10
channel c10: SID=6427 device type=DISK

Starting restore at 11/18/14 11:15:33
Starting implicit crosscheck backup at 11/18/14 11:15:33
Crosschecked 18 objects
Crosschecked 18 objects
Finished implicit crosscheck backup at 11/18/14 11:15:39

Starting implicit crosscheck copy at 11/18/14 11:15:39
Crosschecked 2 objects
Finished implicit crosscheck copy at 11/18/14 11:15:42

searching for all files in the recovery area
cataloging files...
no files cataloged

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +REDO/catdb/datafile/system.260.798798557
channel c1: restoring datafile 00007 to +DATA/catdbstdby/datafile/t24distindex.483.800907503
channel c1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aapnto9s_1_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to +REDO/catdb/datafile/sysaux.261.798798559
channel c2: restoring datafile 00012 to +DATA/catdbstdby/datafile/t24distindex.286.800907661
channel c2: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aepnto9v_1_1
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00004 to +REDO/catdb/datafile/users.263.798798559
channel c3: restoring datafile 00009 to +DATA/catdbstdby/datafile/t24distdata.346.800907611
channel c3: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_adpnto9u_1_1
channel c4: starting datafile backup set restore
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to +REDO/catdb/datafile/undotbs1.262.798798559
channel c4: restoring datafile 00008 to +DATA/catdbstdby/datafile/t24distdata.479.800907597
channel c4: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_abpnto9t_1_1
channel c5: starting datafile backup set restore
channel c5: specifying datafile(s) to restore from backup set
channel c5: restoring datafile 00005 to +ARCHIVE/catdb/datafile/users.5407.800376881
channel c5: restoring datafile 00010 to +DATA/catdbstdby/datafile/t24distdata.288.800907625
channel c5: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_acpnto9t_1_1
channel c6: starting datafile backup set restore
channel c6: specifying datafile(s) to restore from backup set
channel c6: restoring datafile 00006 to +DATA/catdbstdby/datafile/t24distdata.295.800907475
channel c6: restoring datafile 00014 to +DATA/catdbstdby/datafile/undotbs2.285.831499529
channel c6: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a8pnto9r_1_1
channel c7: starting datafile backup set restore
channel c7: specifying datafile(s) to restore from backup set
channel c7: restoring datafile 00011 to +DATA/catdbstdby/datafile/t24distdata.287.800907639
channel c7: restoring datafile 00013 to +DATA/catdbstdby/datafile/t24distindex.358.800907675
channel c7: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a9pnto9s_1_1
channel c2: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aepnto9v_1_1 tag=TAG20141118T110010
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:57
channel c1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aapnto9s_1_1 tag=TAG20141118T110010
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:01:18
channel c3: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_adpnto9u_1_1 tag=TAG20141118T110010
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:02:18
channel c4: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_abpnto9t_1_1 tag=TAG20141118T110010
channel c4: restored backup piece 1
channel c4: restore complete, elapsed time: 00:02:28
channel c5: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_acpnto9t_1_1 tag=TAG20141118T110010
channel c5: restored backup piece 1
channel c5: restore complete, elapsed time: 00:03:47
channel c7: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a9pnto9s_1_1 tag=TAG20141118T110010
channel c7: restored backup piece 1
channel c7: restore complete, elapsed time: 00:04:06
channel c6: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a8pnto9r_1_1 tag=TAG20141118T110010
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:04:27
Finished restore at 11/18/14 11:20:13
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
released channel: c9
released channel: c10

RMAN> list backup of archivelog all;

If the last sequence number listed is n, choose n+1 for complete recovery. In my case the last sequence number listed by the above command is 8291
therefore 8292 is chosen for recovery.

RMAN> recover database until sequence 8292;

Starting recover at 11/18/14 11:22:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5167 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8291
channel ORA_DISK_1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_archive_aopntof3_1_1.arc
channel ORA_DISK_1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_archive_aopntof3_1_1.arc tag=TAG20141118T110258
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_1_seq_8291.2837.863954589 thread=1 sequence=8291
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=2686
channel ORA_DISK_1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_archive_appntof3_1_1.arc
channel ORA_DISK_1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_archive_appntof3_1_1.arc tag=TAG20141118T110258
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_2_seq_2686.3707.863954589 thread=2 sequence=2686
channel default: deleting archived log(s)
archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_1_seq_8291.2837.863954589 RECID=1 STAMP=863954588
unable to find archived log
archived log thread=1 sequence=8292
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/catdbstdby/datafile/system.1827.863954149'

RMAN-00571: =======================================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ======================================================
RMAN-03002: failure of recover command at 11/18/2014 11:23:11
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8292 and starting SCN of 22535896918

RMAN> exit


Recovery Manager complete.

Since we have commanded oracle to recover from a sequence which is not included in the backup, the above error is expected.

STDBY$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 18 11:24:01 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
---------      --------------------            ------------------------
CATDB     MOUNTED               PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER  managed standby database disconnect using current logfile nodelay noparallel ;

Database altered.

SQL>

Check the alert log while you are making some logswitches from both primary instances, mrp process should apply the logfiles.

To check the time gap between the primary and the standby you can use the following query on the standby

SQL>select NAME,VALUE 
from 
v$dataguard_stats 
where name = 'apply lag';
NAME                                                             VALUE
-------------------------                                       ----------------------------
apply lag                                                      +00 00:06:34

Script for Compiling INVALID objects

SET ECHO        OFF
SET FEEDBACK    OFF
SET HEADING     OFF
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

spool compile.sql

SELECT  'alter ' ||
       decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
       ' ' ||
       object_name||
       ' compile' ||
       decode(object_type, 'PACKAGE BODY', ' body;', ';')
FROM   dba_objects
WHERE  status = 'INVALID'
/

spool off

SET ECHO        off
SET FEEDBACK    off
SET HEADING     off
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     on
SET TIMING      off
SET TRIMOUT     on
SET TRIMSPOOL   on
SET VERIFY      off

@compile

SET FEEDBACK    6
SET HEADING     ON

ORA-01031: insufficient privileges

cause for this particular case was
Somebody modified the sqlnet.ora file by putting an entry SQLNET.AUTHENTICATION_SERVICES = (NONE)

This entry is meant to enable one or more authentication services. If authentication has been installed,
then it is recommended that this parameter be set to either none or to one of the authentication methods.
none - for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none,a valid user name and password can be used to access the database.
all  - for all authentication methods.
nts  - for Microsoft Windows native operating system authentication.
Authentication Methods Available with Oracle Advanced Security:
kerberos5  - for Kerberos authentication.
radius - for RADIUS authentication.
tcps  - for SSL authentication.

After making the change  "SLAVE COMMUNICATION ERROR WITH ASM ; TERMINATING PROCESS " observed in the alert log and the database instances went down.,ASM status was intermediate.
Restarting the cluster brought everything up except the ASM instance which complained "Insufficient Privilege to start"
While trying to start the ASM manually "ORA-01031: insufficient privileges message" was thrown.

Finally removing "SQLNET.AUTHENTICATION_SERVICES=(NONE)" entry from the sqlnet.ora file saved the day!

SQLPLUS HANGING

      sqlplus connection might hang due to a number of reasons for instance when maximum number of processes specified by the parameter file reached, when the new sessions need to allocate some memory from shared pool while no memory is available to do so, etc
      If you cannot connect, you have two choices, either wait while the performance is degraded impacting the end users or immediately bounce the instance.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:56:40 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-00020: maximum number of processes (100) exceeded
Enter user-name: ^C
$

To bounce the Instance:

1. We can directly kill the process using unix id

First list the process id for that particular instance like this
$ps -fe|grep pmon
oracle 2031994     1    0  Oct 22 -  4:18 ora_pmon_TESTDB

Kill that particular instance by its unix process id like this
$kill -9 2031994
 And then export the instance and startit up once again
$export ORACLE_SID=TESTDB
$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:59:43 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conected to an idle instance.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>

2.Alternatively you can use the –prelim option 
-prelim option stands for "preliminary connection".

       When we log on normally including SYSDBA connections, the following 3 steps are completed
1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
3. The new process allocates process and session state objects and initializes new session structures in SGA

        But when we use the prelim option only the first two steps are completed. Since allocating memory is not a possible operation on shared memory structures that are protected by some sort of locks usually latches or Oracle KGX mutexes

$sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:13:24 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> shutdown abort
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>
 Bouncing the instance is only possible while it is affordable, What about for 24/7 databases,still there is a work around provided by oracle to run the ORADEBUG commands while you are connected to a running database using the prilim option
$sqlplus -prelim "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:33:09 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> oradebug hanganalyze 3

Statement processed.

The command seems successful,However when looking into the resulting tracefile, we see this:

Processing Oradebug command 'HANGANALYZE 3'
====================================================================
HANG ANALYSIS:

ERROR: Can not perform hang analysis dump without a process
       state object and a session state object.
  ( process=(nil), sess=(nil) )

=====================================================================
We still can do this
SQL> oradebug setmypid
Statement processed.

SQL> oradebug hanganalyze 12
Hang Analysis in /u01/app/oracle/diag/rdbms/testdb/TESTDB_ora_1234.trc

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

LSNRCTL>lsnrctl status

Service "orcl" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...

SQL> conn scott/tiger@orcl

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

$rman target sys/password@PRODDB auxiliary sys/password@TESTDB

RMAN-04006: error from auxiliary database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

SOLUTIONS

The best place to start with is to check the tnsnames.ora and listener.ora files and make sure that all is well.

1. By passing oracle Net authentication  using operating system authentication for auxiliary database.

$export ORACLE_SID=TESTDB
rman target sys/password@PRODDB auxiliary /
instead of 
rman target sys/password@PRODDB auxiliary sys/password@TESTDB

2. But if still Oracle Net authentication is required put UR=A entry in the tnsnames.ora file to take advantage of using restricted session privilege across the network.

3.  To statically configure service information for the listener put the following entry in the listener.ora file  
     (SID_DESC=
        (GLOBAL_DBNAME=TESTDB)
        (SID_NAME=TESTDB)
        (ORACLE_HOME=/oracle/base/dbhome01/)
      )

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