How to perform "Cloning" through "Active database" in Oracle 11g

In my case, I am using below databases for cloning purpose

Source Database : - prod
Target Database : - karan

Step 1) Set the environment through a source server.

Example: - 
[oracle@prod ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle

Step 2) Login through sqlplus and start the database, make sure you start your database using spfile.

Example: -
[oracle@prod ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 18 20:53:01 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
Database mounted.
Database opened.

Step 3) Make sure your database should in archive mode.

Example: -
SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     47
Next log sequence to archive   49
Current log sequence        49

Step 4) Now check the recovery parameter has set properly or not

Example: -
SQL> show parameter recover

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest      string /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size      big integer 4182M
db_unrecoverable_scn_tracking      boolean TRUE
recovery_parallelism      integer 0

In my case, both parameters have been set correctly 

*db_recovery_file_dest
*db_recovery_file_dest_size

Step 5) Now set "datafile convert" and "logfile convert" parameter, but before setting the parameters need to check the locations of data files and log files.

Example: -

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------
/u01/app/oracle/oradata/prod/users01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod/system01.dbf

SQL> select member from v$logfile;

MEMBER
------------------------------------------------------
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log

Set the parameters for datafiles and log files.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/karan/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/karan/' scope=spfile;

System altered.

Step 6) Create pfile from spfile.

SQL> create pfile from spfile;

File created.

Step 7) Shut down the database and start again to reflect the changes in spfile, Now we can see below our values has been changed.

Example: -
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> show parameter db_file_name_convert;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert      string /u01/app/oracle/oradata/prod/,
  /u01/app/oracle/oradata/karan
 
SQL> show parameter log_file_name_convert;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert      string /u01/app/oracle/oradata/prod/,
  /u01/app/oracle/oradata/karan

Step 8) Configure the Listener and tnsnames to resolve the connection from Source to Target servers.

Source Listener File

Location: - ORACLE_HOME/network/admin


[oracle@prod admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod.radical.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
      (SID_DESC=
          (GLOBAL_DBNAME=karan)
          (SID_NAME=karan)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
)
      )

Source tnsnames.ora files 

Location: - ORACLE_HOME/network/admin

[oracle@prod admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. 
 PROD = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
 ) 
 (CONNECT_DATA = 
 (SERVICE_NAME = prod) 
 ) 
 )
KARAN = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
) 
(CONNECT_DATA = 
(SERVICE_NAME = karan) 
(UR = A) 
 ) 
)   

Start the listener

[oracle@prod admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:35:13

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prod/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-Jan-2019 21:35:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/prod/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.radical.com)(PORT=1521)))
Services Summary...
Service "karan" has 1 instance(s).
  Instance "karan", status UNKNOWN, has 3 handler(s) for this service...
Service "prod" has 1 instance(s).
  Instance "prod", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully


Step 9) Target Server:
Add the database entry in /etc/oratab file and Configure a listener, tnsnames as shown below for RMAN to be able to access the auxiliary database (Karan) for duplication.

[oracle@dr ~]$ cat /etc/oratab
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should, "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
karan:/u01/app/oracle/product/11.2.0/db_1:N

Target Listener file output: -

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dr.radical.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod)
          (SID_NAME=prod)                      
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
(SID_DESC=
          (GLOBAL_DBNAME=karan)
          (SID_NAME=karan)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
         (PRESPAWN_MAX=20)
         (PRESPAWN_LIST=
           (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
         )
       )
   )

Target tnsnames file output: -

[oracle@dr admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )
KARAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = karan)
    )
  )

Start the listener: -

[oracle@dr admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:45:38

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dr/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dr.radical.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-Jan-2019 21:45:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dr.radical.com)(PORT=1521)))
Services Summary...
Service "karan" has 1 instance(s).
  Instance "karan", status UNKNOWN, has 3 handler(s) for this service...
Service "prod" has 1 instance(s).
  Instance "prod", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully

Step 10) Make sure tnsping should be resolved the connection from both the servers.

Source Server Output: -

[oracle@prod admin]$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:49:58

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod)))
OK (0 msec)
[oracle@prod admin]$ tnsping karan

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:50:00

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = karan) (UR = A)))
OK (10 msec)

Target Server Output: -

[oracle@dr admin]$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:50:54

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod)))
OK (20 msec)
[oracle@dr admin]$ tnsping karan

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-JAN-2019 21:50:56

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = karan)))
OK (0 msec)

Step 11) Copy the parameter file and password file from source to target server.

Example: -

Parameter file; -
[oracle@prod dbs]$ scp initprod.ora oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/initkaran.ora
oracle@192.168.2.21's password: 
initprod.ora                                                                                                                         100% 1366     1.3KB/s   00:00    

Password File: -
[oracle@prod dbs]$ scp orapwprod oracle@192.168.2.21:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwkaran
oracle@192.168.2.21's password: 
orapwprod                                                                                                                            100% 1536     1.5KB/s   00:00    
[oracle@prod dbs]$ 

Step 12) From Target Server edit the parameter file and create the required directories as below. Once done start the " Karan" database in nomount mode.

Target Server: -

Output of Parameter File: -
[oracle@dr dbs]$ cat initkaran.ora 
karan.__db_cache_size=348127232
karan.__java_pool_size=4194304
karan.__large_pool_size=8388608
karan.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
karan.__pga_aggregate_target=339738624
karan.__sga_target=503316480
karan.__shared_io_pool_size=0
karan.__shared_pool_size=134217728
karan.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/karan/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/karan/control01.ctl','/u01/app/oracle/fast_recovery_area/karan/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/karan/'
*.db_name='karan'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=karanXDB)'
*.log_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/karan/'
*.memory_target=840957952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Necessary directories need to create: -
[oracle@dr dbs]$ mkdir -p /u01/app/oracle/admin/karan/adump
[oracle@dr dbs]$ mkdir -p /u01/app/oracle/oradata/karan
[oracle@dr dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/karan

Start the target database in nomount mode: -

[oracle@dr dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 18 22:01:35 2019

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
SQL> 

Create spfile from pfile to start the database from spfile: -

SQL> create spfile from pfile;

File created.

Shut Down the database and again start in a nomount stage, now it will start using spfile: -

SQL> shut immediate
ORA-01507: database not mounted


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

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
SQL> 
SQL> 
SQL> show parameter spfile;

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile      string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfilekaran.ora


Step 13) From source server connect with the target as well as an auxiliary database to complete the cloning part with the help of RMAN

Source Server: -

[oracle@prod dbs]$ rman target / auxiliary sys/sys@karan

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 18 22:08:17 2019

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

connected to target database: PROD (DBID=407246072)
connected to auxiliary database: KARAN (not mounted)


RMAN> duplicate target database to 'Karan' from active database;

Starting Duplicate Db at 18-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''KARAN'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/karan/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/karan/control02.ctl' from 
 '/u01/app/oracle/oradata/karan/control01.ctl';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

Starting backup at 18-JAN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f tag=TAG20190118T221056 RECID=3 STAMP=997913457
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JAN-19

Starting restore at 18-JAN-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 18-JAN-19

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/karan/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/karan/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/karan/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/karan/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/karan/goms01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/karan/goms_data01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/karan/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/karan/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/karan/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/karan/users01.dbf"   datafile 
 5 auxiliary format 
 "/u01/app/oracle/oradata/karan/goms01.dbf"   datafile 
 6 auxiliary format 
 "/u01/app/oracle/oradata/karan/goms_data01.dbf"   ;
   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

executing command: SET NEWNAME

Starting backup at 18-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
output file name=/u01/app/oracle/oradata/karan/system01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output file name=/u01/app/oracle/oradata/karan/sysaux01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output file name=/u01/app/oracle/oradata/karan/undotbs01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prod/goms01.dbf
output file name=/u01/app/oracle/oradata/karan/goms01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/prod/goms_data01.dbf
output file name=/u01/app/oracle/oradata/karan/goms_data01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
output file name=/u01/app/oracle/oradata/karan/users01.dbf tag=TAG20190118T221108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-JAN-19

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/fast_recovery_area/PROD/archivelog/2019_01_18/o1_mf_1_52_g440n0hz_.arc" auxiliary format 
 "/u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 18-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=52 RECID=64 STAMP=997913689
output file name=/u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_0otnlt2q_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 18-JAN-19

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_0otnlt2q_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_0otnlt2q_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/goms01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=997913695 file name=/u01/app/oracle/oradata/karan/goms_data01.dbf

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

executing command: SET until clause

Starting recover at 18-JAN-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file /u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_0otnlt2q_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/KARAN/archivelog/2019_01_18/o1_mf_1_52_0otnlt2q_.arc thread=1 sequence=52
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-JAN-19
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''KARAN'' 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 =  ''KARAN'' 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     839282688 bytes

Fixed Size                     2257880 bytes
Variable Size                545262632 bytes
Database Buffers             289406976 bytes
Redo Buffers                   2355200 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "KARAN" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/karan/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/karan/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/karan/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/karan/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/karan/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/karan/sysaux01.dbf", 
 "/u01/app/oracle/oradata/karan/undotbs01.dbf", 
 "/u01/app/oracle/oradata/karan/users01.dbf", 
 "/u01/app/oracle/oradata/karan/goms01.dbf", 
 "/u01/app/oracle/oradata/karan/goms_data01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/karan/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/karan/sysaux01.dbf RECID=1 STAMP=997913713
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/karan/undotbs01.dbf RECID=2 STAMP=997913713
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/karan/users01.dbf RECID=3 STAMP=997913713
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/karan/goms01.dbf RECID=4 STAMP=997913713
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/karan/goms_data01.dbf RECID=5 STAMP=997913713

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=997913713 file name=/u01/app/oracle/oradata/karan/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=997913713 file name=/u01/app/oracle/oradata/karan/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=997913713 file name=/u01/app/oracle/oradata/karan/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=997913713 file name=/u01/app/oracle/oradata/karan/goms01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=997913713 file name=/u01/app/oracle/oradata/karan/goms_data01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database force logging

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

database opened
Finished Duplicate Db at 18-JAN-19

RMAN> 

Step 14) Check whether the database is up and running in Target side

[oracle@dr dbs]$ ps -ef | grep pmon
oracle    5015     1  0 22:15 ?        00:00:00 ora_pmon_karan
oracle    5168  4206  0 22:19 pts/0    00:00:00 grep pmon


[oracle@dr dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 18 22:19:40 2019

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, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
KARAN   READ WRITE

My database is up and running in target side.

Thank you!!








Comments

Post a Comment

Popular posts from this blog

How to Add, Resize, Drop and Rename of a datafile in Tablespace

Image
                                       How to Add, Resize, Drop and Rename a datafile in Tablespace In order to create a data file first, need to create a tablespace, To know how to create a tablespace  Click here To demonstrate, I will use USERS tablespace to Add,  Resize, Drop and Rename a data file. First, we can check how many data files are present under USERS tablespace through below command. Command: - select name from v$datafile; We can see from above output only a single data file is present under USERS tablespace. Addition of Datafile: - Command to add a datafile: - alter tablespace users add datafile '/u01/app/oracle/oradata/prod/users02.dbf' size 50m; The new data file has been added to my USERS tablespace. Resize of a Datafile: - Command to resize the datafile: - alter database datafile '/u01/app/oracle/oradata/prod/users02.dbf' resize 20m; Renaming of a Datafile: - We have a few steps to rename a data file: -
Read more

How to do "Multiplexing of Control Files" through spfile ( server parameter file) in Oracle 11g

Image
Step 1)  To perform "Multiplexing of Control Files" through spfile ( server parameter file)" first we need to create spfile from pfile. Example to create spfile from pfile: -                                       Step 2) Copy the control files in physical location from 'cp' command. Note: - At present, I have 2 control files,  I will add one more Control files in the same location. Example: -  Now you can see from above output control04.ctl file has been physically added successfully under /u01/app/oracle/oradata/prod/control04.ctl location. Step 3)  Now you need to set the same through alter command. a)  first shut down the database                                          b)  Startup (Start the database through spfile)                                                                         Step 4) Now through SQL prompt alter the changes. Example: - Step 5) Now shutdown and start the database again and check
Read more

Oracle "Redo log Groups addition/deletion" | Karan Rajpoot

Image
                    Oracle "Redo log Groups addition/deletion" Step 1) To add redo log group first we will check the log groups, members details, location, and size. Example: - From the above output, we can see we have 3 groups and inside every group, we have 1 member each of size 50 MB and the location is /u01/app/oracle/oradata/karan/ Step 2) Now we will add one group and check the status of that group. Example: - Now again we can check the details of log groups and member. Now the 4th group and a member of 50 MB has been added successfully. Step 3) Now I will add one more member to the 4th log group. Example: - We can see from the above output that in group 4, now 2 members are there. Steps to drop the latest added group and member Note: - We cannot drop CURRENT members as shown in the above output, So I will add a new member in group 3 and then I will drop that member. Step 1)  Addition of new
Read more