11g Active Cloning

Source Database: prod
Target Database: stage

Perform below steps on Target Database:
Presteps:
Check out for passwords and save passwords
================================================
>sqlplus
>/as sysdba
SQL> select name from v$database;
Spooling the usernames and passwords along with alter syntax
********************************************************
set echo off
set head off
set pagesize 0
set feedback off
spool alter.sql
select 'alter user '||NAME||' identified by values '''||password||''';' from sys.USER$;
spool off

Spooling the create user’s script (in case the users won’t be available after refresh)
**********************************************************************
set echo off
set head off
set pagesize 0
set feedback off
spool pre_refresh_user.sql
select 'create user '||username||'  identified by values '''||b.password||'''  default tablespace  '||default_tablespace||'  temporary tablespace   '||temporary_tablespace||'  profile default ; ' from dba_users a, user$ b
where a.username=b.name and a.username in (
select username from dba_users where username like  '%%' )  ;
spool off

Spooling the pre refresh privs of users
*********************************
spool sys_privs.sql
select 'grant '||privilege||' to '||grantee||' ; '  from dba_sys_privs where grantee in (
select username from dba_users where username like '%%' ) ;
spool off

Spooling the pre refresh roles on grantees
************************************
spool pre_refresh_role_privs.sql
select 'grant '||granted_role||' to '||grantee||' ; '  from dba_role_privs where grantee like '%%';
spool off

Spooling the pre refresh grants on tables
************************************
spool owner.sql
select 'grant '||privilege||' on '||owner||'.'||table_name||' to  '||grantee||' ; ' from dba_tab_privs ;
spool off

Spooling the DB_Links.
********************

Spool DB_Links.sql

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)   
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''  
||chr(10)||';' TEXT
FROM  sys.link$       L,     
sys.user$       U
WHERE L.OWNER# = U.USER# ;

###############################################
Verify the below parameters
SQL> show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE

Need to make cluster_database parameter false to do active database duplicate if target is cluster database.
SQL>  alter system set cluster_database=false scope=spfile sid='*';
System altered.
********************************************************************************
Take the backup of pfile:
SQL> create pfile='location' from spfile;
********************************************************************
Shutdown the cluster database using srvctl utility and bring any one of the instance with mount restrict state.

srvctl stop database -d db_name
on instance1:
sql>startup mount pfile='location of backup pfile taken in previous step' restrict;
SQL> select name from v$database;
SQL> drop database;
Database dropped.
***********************************************************************
Startup the database with nomount stage.
sql>startup nomount pfile='location of backup pfile taken in previous step' ;
***********************************************************************
Create new password file as same as production password
take backup of password file on target database and create a new password file with the source database password
**************************************************************************
check the network connectivity from source db to target and target db to source db
*****************************************************************************
connect to rman:
$rman target sys/xxxxxxx@prod auxiliary sys/xxxxxxx@stage

RMAN>run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate AUXILIARY channel t1 DEVICE TYPE DISK;
allocate AUXILIARY channel t2 DEVICE TYPE DISK;
allocate AUXILIARY channel t3 DEVICE TYPE DISK;
duplicate target database to stage from active database;
release channel t1;
release channel t2;
release channel t3;
release channel c2;
release channel c1;
release channel c3;
}
EOF
exit;

**************************************************************************************
Poststeps:

Create new spfile and enable cluster mode
restart the database with srvctl utility
run the scripts taken while pre steps
Disable the archive mode
verify  the database name