Tag Archives: RMAN ACTIVE DUPLICATION ASM TO ASM

RMAN ACTIVE DUPLICATION ASM TO ASM

RMAN ACTIVE DUPLICATION ASM TO ASM

AIM: RMAN Cloning using ACTIVE duplicate command from BR8PROD to BR8DEV without downtime.

0. Overview
1. Environment
2. Create Pfile and Edit (RAC1)
3. Copy password file/initBR8DEV.ora to auxiliary side
4. Create required directories (On the auxiliary host)
5. Add oratab entry (On the auxiliary host)
6. Add both TNS Entries on both sides (***Mandatory)
7. Static Registration on LISTENER (On the auxiliary host)
8. Start the auxilary instance (On the auxiliary host)
9. TNS Verification
10. RMAN Connectvity Verification (On the auxiliary host)
11. Run RMAN Duplicate (On the auxiliary host)
12. Verify
13. Create the spfile in ASM disk


0. Overview

It is a new feature in Oracle 11g where cloning is done from one database to another database without any outage or downtime of the source (Primary) database.

Earlier we use to clone the database using cold back or rman backup. But RMAN Active duplication feature allows a database to be duplicated directly from its live source database instead of using its backup.

RMAN directly reads the data from the database using source (Primary) database CONTROLFILE.


1. Environment

Source database:

Database name: BR8PROD
Archivelog Mode: ON
RAC: No (Oracle Restart)
Version: 11.2.0.4
Hostname: rac1.rajasekhar.com
Filesystem: ASM
Diskgroup: +DATA1,FRA
GI_HOME: /u01/app/11.2.0/grid
ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

SQL> select name, open_mode, dbid, created from v$database;

NAME      OPEN_MODE                  DBID CREATED
--------- -------------------- ---------- --------------------
BR8PROD   READ WRITE           4192214970 23-NOV-2016 01:43:22

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ------------------------------------
BR8PROD          rac1.rajasekhar.com


SQL> select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
/  2    3    4    5    6    7    8

NAME
--------------------------------------------------
+DATA1/br8prod/controlfile/current.260.928633403
+DATA1/br8prod/datafile/example.265.928633413
+DATA1/br8prod/datafile/sysaux.257.928633345
+DATA1/br8prod/datafile/system.256.928633345
+DATA1/br8prod/datafile/undotbs1.258.928633345
+DATA1/br8prod/datafile/users.259.928633345
+DATA1/br8prod/onlinelog/group_1.261.928633405
+DATA1/br8prod/onlinelog/group_2.262.928633405
+DATA1/br8prod/onlinelog/group_3.263.928633407
+DATA1/br8prod/tempfile/temp.264.928633411

10 rows selected.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
BR8PROD.RAJASEKHAR.COM

SQL>

Target database (On the auxiliary host):


Database name: BR8DEV
Archivelog Mode: ON
RAC: No (Oracle Restart)
Version: 11.2.0.4
Hostname: rac2.rajasekhar.com
Filesystem: ASM
Diskgroup: +DATA,ARCH
GI_HOME: /u01/app/11.2.0/grid
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4/db_1


2. Create Pfile and Edit (RAC1)

SQL> show parameter pfile

NAME      TYPE        VALUE
--------- ----------- ------------------------------
spfile    string      +DATA1/br8prod/spfilebr8prod.ora

SQL> create pfile='/home/oracle/initBR8PROD.ora' from spfile;

File created.

SQL>


[oracle@rac1 ~]$ cat /home/oracle/initBR8PROD.ora

BR8PROD.__db_cache_size=486539264
BR8PROD.__java_pool_size=16777216
BR8PROD.__large_pool_size=33554432
BR8PROD.__pga_aggregate_target=520093696
BR8PROD.__sga_target=754974720
BR8PROD.__shared_io_pool_size=0
BR8PROD.__shared_pool_size=201326592
BR8PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/BR8PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/br8prod/controlfile/current.260.928633403'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain='RAJASEKHAR.COM'
*.db_name='BR8PROD'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8PRODXDB)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


Edited Parameter File for Auxiliary Database:

cat initBR8DEV.ora

BR8DEV.__db_cache_size=486539264
BR8DEV.__java_pool_size=16777216
BR8DEV.__large_pool_size=33554432
BR8DEV.__pga_aggregate_target=520093696
BR8DEV.__sga_target=754974720
BR8DEV.__shared_io_pool_size=0
BR8DEV.__shared_pool_size=201326592
BR8DEV.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/BR8DEV/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+ARCH'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='RAJASEKHAR.COM'
*.db_name='BR8DEV'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8DEVXDB)'
*.log_archive_dest_1='LOCATION=+ARCH'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATA1','+DATA'
*.log_file_name_convert='+DATA1','+DATA'


3. Copy password file/initBR8DEV.ora to auxiliary side

[oracle@rac1 ~]$ scp initBR8DEV.ora oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs
initBR8DEV.ora                 100%  789     0.8KB/s   00:00
[oracle@rac1 ~]$

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ scp orapwBR8PROD oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs
orapwBR8PROD                    100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$

[oracle@rac2 dbs]$ mv orapwBR8PROD orapwBR8DEV
[oracle@rac2 dbs]$ ls -ltr orapwBR8DEV
-rw-r----- 1 oracle oinstall 1536 Nov 23 13:56 orapwBR8DEV
[oracle@rac2 dbs]$


4. Create required directories (On the auxiliary host)

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/BR8DEV/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle
[oracle@rac2 ~]$


5. Add oratab entry (On the auxiliary host)

[oracle@rac2 ~]$ cat /etc/oratab | grep -i BR8DEV
BR8DEV:/u01/app/oracle/product/11.2.0.4/db_1:N
[oracle@rac2 ~]$


6. Add both TNS Entries on both sides (***Mandatory)

RAC1:

BR8DEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8DEV.rajasekhar.com)
    )
  )

BR8PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)
    )
  )

RAC2:

BR8DEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8DEV.rajasekhar.com)
    )
  )


BR8PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)
    )
  )


7. Static Registration on LISTENER (On the auxiliary host)

On RAC2 – Add below entry to listener.ora on auxiliary side

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = BR8DEV)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM)
    )
  )

Before

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
[oracle@rac2 ~]$

[oracle@rac2 ~]$ ps -ef | grep tns
root         9     2  0 13:33 ?        00:00:00 [netns]
oracle    3428     1  0 13:36 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    4197  3369  0 14:35 pts/1    00:00:00 grep tns
[oracle@rac2 ~]$
[oracle@rac2 ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:09

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2016 13:36:25
Uptime                    0 days 0 hr. 58 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$

After

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = BR8DEV)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM)
    )
  )

[oracle@rac2 ~]$

[oracle@rac2 ~]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
The command completed successfully
[oracle@rac2 ~]$
[oracle@rac2 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:51:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2016 13:36:25
Uptime                    0 days 1 hr. 15 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "BR8DEV.RAJASEKHAR.COM" has 1 instance(s).
  Instance "BR8DEV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$


8. Start the auxiliary instance (On the auxiliary host)

[oracle@rac2 ~]$ . oraenv 
BR8DEV
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:44:39 2016

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initBR8DEV.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> 


9. TNS Verification

From RAC1:

[oracle@rac1 ~]$ tnsping BR8PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:39

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping BR8DEV

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:49

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com)))
OK (0 msec)
[oracle@rac1 ~]$

From RAC2:

[oracle@rac2 ~]$ tnsping BR8DEV

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:46

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping BR8PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:53

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)))
OK (0 msec)
[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus sys/sys@BR8DEV as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:22 2016

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


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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


[oracle@rac2 ~]$ sqlplus sys/sys@BR8PROD as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:34 2016

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


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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$


10. RMAN Connectivity Verification (On the auxiliary host)

restore_connectivity.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
ORACLE_SID=BR8DEV
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_connectivity.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target sys/sys@BR8PROD
connect auxiliary sys/sys@BR8DEV
run{
allocate channel t1 type disk;
allocate auxiliary channel a1 device type disk;
release channel t1;
}
exit;
EOF

Please click here to download script <-----

[oracle@rac2 ~]$ chmod 775 restore_connectivity.sh

[oracle@rac2 ~]$ ./restore_connectivity.sh

RMAN> RMAN> RMAN> 2> 3> 4> 5> RMAN> [oracle@rac2 ~]$

Output

[oracle@rac2 ~]$ cat restore_connectivity.log

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 15:09:25 2016

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

RMAN>
connected to target database: BR8PROD (DBID=4192214970)

RMAN>
connected to auxiliary database: BR8DEV (not mounted)

RMAN> 2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=25 device type=DISK

allocated channel: a1
channel a1: SID=23 device type=DISK

released channel: t1
released channel: a1

RMAN>

Recovery Manager complete.
[oracle@rac2 ~]$


11. Run RMAN Duplicate (On the auxiliary host)

[oracle@rac2 ~]$ cat restore.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
ORACLE_SID=BR8DEV
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target sys/sys@BR8PROD
connect auxiliary sys/sys@BR8DEV
run{
allocate channel t1 type disk;
allocate auxiliary channel a1 device type disk;
duplicate target database to BR8DEV from active database nofilenamecheck;
release channel t1;
}
exit;
EOF
[oracle@rac2 ~]$

Please click here to download the script <----

[oracle@rac2 ~]$ chmod 775 restore.sh
[oracle@rac2 ~]$

[oracle@rac2 ~]$ nohup ./restore.sh &
[1] 5483
[oracle@rac2 ~]$ nohup: appending output to `nohup.out'

[oracle@rac2 ~]$ jobs -l
[1]+  5483 Running                 nohup ./restore.sh &
[oracle@rac2 ~]$

tail -f restore_db.log <--- you can monitor progress

output

[oracle@rac2 ~]$ cat restore_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 16:09:06 2016

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

RMAN>
connected to target database: BR8PROD (DBID=4192214970)

RMAN>
connected to auxiliary database: BR8DEV (not mounted)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=50 device type=DISK

allocated channel: a1
channel a1: SID=24 device type=DISK

Starting Duplicate Db at 23-NOV-16

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''BR8PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BR8DEV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+DATA/br8dev/controlfile/current.261.928685355';
   restore clone controlfile to  '+ARCH/br8dev/controlfile/current.263.928685355' from
 '+DATA/br8dev/controlfile/current.261.928685355';
   sql clone "alter system set  control_files =
  ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''BR8PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''BR8DEV'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

Starting backup at 23-NOV-16
channel t1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BR8PROD.f tag=TAG20161123T160920 RECID=2 STAMP=928685361
channel t1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 23-NOV-16

Starting restore at 23-NOV-16

channel a1: copied control file copy
Finished restore at 23-NOV-16

sql statement: alter system set  control_files =   ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   backup as copy reuse
   datafile  1 auxiliary format
 "+data"   datafile
 2 auxiliary format
 "+data"   datafile
 3 auxiliary format
 "+data"   datafile
 4 auxiliary format
 "+data"   datafile
 5 auxiliary format
 "+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-NOV-16
channel t1: starting datafile copy
input datafile file number=00001 name=+DATA1/br8prod/datafile/system.256.928633345
output file name=+DATA/br8dev/datafile/system.262.928685383 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:46
channel t1: starting datafile copy
input datafile file number=00002 name=+DATA1/br8prod/datafile/sysaux.257.928633345
output file name=+DATA/br8dev/datafile/sysaux.263.928685427 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:35
channel t1: starting datafile copy
input datafile file number=00005 name=+DATA1/br8prod/datafile/example.265.928633413
output file name=+DATA/br8dev/datafile/example.264.928685463 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:25
channel t1: starting datafile copy
input datafile file number=00003 name=+DATA1/br8prod/datafile/undotbs1.258.928633345
output file name=+DATA/br8dev/datafile/undotbs1.265.928685487 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:07
channel t1: starting datafile copy
input datafile file number=00004 name=+DATA1/br8prod/datafile/users.259.928633345
output file name=+DATA/br8dev/datafile/users.266.928685495 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-NOV-16

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/br8prod/archivelog/2016_11_23/thread_1_seq_5.260.928685495" auxiliary format
 "+ARCH"   ;
   catalog clone start with  "+ARCH";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 23-NOV-16
channel t1: starting archived log copy
input archived log thread=1 sequence=5 RECID=3 STAMP=928685496
output file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 RECID=0 STAMP=0
channel t1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 23-NOV-16

searching for all files that match the pattern +ARCH

List of Files Unknown to the Database
=====================================
File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497
File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009
File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009
File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095
File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095
File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163
File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163
File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497

List of Files Which Where Not Cataloged
=======================================
File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355
  RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=928685500 file name=+DATA/br8dev/datafile/system.262.928685383
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=928685500 file name=+DATA/br8dev/datafile/sysaux.263.928685427
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=928685500 file name=+DATA/br8dev/datafile/undotbs1.265.928685487
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=928685500 file name=+DATA/br8dev/datafile/users.266.928685495
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=928685500 file name=+DATA/br8dev/datafile/example.264.928685463

contents of Memory Script:
{
   set until scn  996825;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-NOV-16

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file +ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497
archived log file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-NOV-16
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BR8DEV'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''BR8DEV'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BR8DEV" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+data' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+data' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+data' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/br8dev/datafile/system.262.928685383'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/br8dev/datafile/sysaux.263.928685427",
 "+DATA/br8dev/datafile/undotbs1.265.928685487",
 "+DATA/br8dev/datafile/users.266.928685495",
 "+DATA/br8dev/datafile/example.264.928685463";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/sysaux.263.928685427 RECID=1 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/undotbs1.265.928685487 RECID=2 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/users.266.928685495 RECID=3 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/example.264.928685463 RECID=4 STAMP=928685514

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=928685514 file name=+DATA/br8dev/datafile/sysaux.263.928685427
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=928685514 file name=+DATA/br8dev/datafile/undotbs1.265.928685487
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=928685514 file name=+DATA/br8dev/datafile/users.266.928685495
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=928685514 file name=+DATA/br8dev/datafile/example.264.928685463

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 23-NOV-16

released channel: t1
released channel: a1

RMAN>

Recovery Manager complete.
[oracle@rac2 ~]$


12. Verify

SQL> select name, open_mode, dbid, created from v$database;

NAME      OPEN_MODE                  DBID CREATED
--------- -------------------- ---------- --------------------
BR8DEV    READ WRITE           3533049546 23-NOV-2016 16:11:54

SQL> select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
/   2    3    4    5    6    7    8

NAME
--------------------------------------------------------------------------------
+ARCH/br8dev/controlfile/current.263.928685355
+DATA/br8dev/controlfile/current.261.928685355
+DATA/br8dev/datafile/example.264.928685463
+DATA/br8dev/datafile/sysaux.263.928685427
+DATA/br8dev/datafile/system.262.928685383
+DATA/br8dev/datafile/undotbs1.265.928685487
+DATA/br8dev/datafile/users.266.928685495
+DATA/br8dev/onlinelog/group_1.267.928685515
+DATA/br8dev/onlinelog/group_2.268.928685515
+DATA/br8dev/onlinelog/group_3.269.928685517
+DATA/br8dev/tempfile/temp.270.928685523

11 rows selected.

SQL>


13. Create the spfile in ASM disk.

SQL> show parameter pfile

NAME     TYPE        VALUE
-------- ----------- ------------------------------
spfile   string      /u01/app/oracle/product/11.2.0
                     .4/db_1/dbs/spfileBR8DEV.ora
SQL>

[oracle@rac2 ~]$ ls -ltr /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora
ls: /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora: No such file or directory
[oracle@rac2 ~]$

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ asmcmd
ASMCMD> cd DATA/BR8DEV
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    CONTROLFILE/
                                        Y    DATAFILE/
                                        Y    ONLINELOG/
                                        Y    TEMPFILE/
ASMCMD> mkdir PARAMETERFILE
ASMCMD> cd PARAMETERFILE
ASMCMD> pwd
+DATA/BR8DEV/PARAMETERFILE
ASMCMD>

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
SQL> create spfile='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora' from PFILE;

File created.

SQL>

ASMCMD> pwd
+DATA/BR8DEV/PARAMETERFILE
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 23 23:00:00  Y    spfile.271.928710147
                                                 N    spfileBR8DEV.ora => +DATA/BR8DEV/PARAMETERFILE/spfile.271.928710147
ASMCMD>

[oracle@rac2 dbs]$ cp initBR8DEV.ora initBR8DEV.ora.bkp

modify initBR8DEV.ora with spfile location 

[oracle@rac2 dbs]$ cat initBR8DEV.ora
SPFILE='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora'
[oracle@rac2 dbs]$

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/br8dev/parameterfile/spf
                                                 ilebr8dev.ora
SQL>

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA, +ARCH
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> ALTER SYSTEM SET CONTROL_FILES='+ARCH/br8dev/controlfile/current.263.928685355','+DATA/br8dev/controlfile/current.261.928685355' scope=spfile;

System altered.

SQL>

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
BR8DEV    READ WRITE

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala