Thursday, 19 April 2018

Cloning From Non-ASM Database to ASM Database

Environment:-

------------------------------------------------
Source Database:- LIGHT
Source DB Structure:- Non-ASM
Source Server:- servera.oracle.com

Target Database:- LIGHT
Target DB Structure:- ASM
ASM Instance:- +ASM
Target Server:- ora11gr2asm.localdomain
------------------------------------------------


Steps:-

1. Existing database Configurations in Non-ASM server.
2. Target Server Setup and Precheck.
3. Make entry in /etc/hosts in both servers for connectivity.
4. Backup the pfile,password file and transfer to ASM server ORACLE_HOME/dbs location.
5. Take full backup of  Non-ASM database and transfer to ASM server.
6. Make entry in /etc/orarab and set the sid and home.
7. Edit the pfile with required chnages using ASM file system.
8. Make static listener in ASM server.
9. Make tns entry for both the database (ASM db and Non-ASM db).
10. Make spfile from the pfile and modify the pfile accordingly.
11. Start the ASM database in nomount mode.
12. Connect with rman prompt with target and auxiliary database and start duplicate.
13. Add the database to ASM configuration using srvctl and start the db using srvctl.
14. Check the final database configuration.
====================================================================


Step:-1. Existing database Configurations in Non-ASM server.


[oracle@servera ~]$ ps -ef|grep pmon
oracle    4567     1  0 17:25 ?        00:00:00 ora_pmon_LIGHT
oracle    4653  4519  0 17:26 pts/2    00:00:00 grep pmon

[oracle@servera ~]$ ps -ef|grep tns
oracle    4558     1  0 17:24 ?        00:00:00 /u01/app/oracle/product/11.2.0.4/db_1/bin/tnslsnr LISTENER -inherit
oracle    4655  4519  0 17:26 pts/2    00:00:00 grep tns


NAME      OPEN_MODE            LOG_MODE     DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT     READ WRITE           ARCHIVELOG   PRIMARY

INSTANCE_NAME    LOGINS     HOST_NAME            DB Startup Time
---------------- ---------- -------------------- --------------------
LIGHT            ALLOWED    servera.oracle.com   19-APR-2018 17:24:59


TABLESPACE_NAME                               size MB     free MB % used
---------------------------------------- ------------ ----------- ------
HELLO                                              30          29      3
INTRA                                              45          43      4
LILLO                                              36          34      5
USERS                                               5           4     26
UNDOTBS1                                           30          15     51
SYSAUX                                            510          32     93
SYSTEM                                            740           3     99
TEMP **TEMP**                                      20           0    100

8 rows selected.


TABLESPACE_NAME              FILE_ID FILE_NAME                                                       SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
USERS                              4 /u01/app/oracle/oradata/LIGHT/users01.dbf                    .004882813 YES
UNDOTBS1                           3 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf                  .029296875 YES
SYSAUX                             2 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf                   .498046875 YES
SYSTEM                             1 /u01/app/oracle/oradata/LIGHT/system01.dbf                    .72265625 YES
HELLO                              5 /u01/app/oracle/oradata/LIGHT/hello01.dbf                    .029296875 NO
LILLO                              6 /u01/app/oracle/oradata/LIGHT/lillo01.dbf                    .025390625 NO
LILLO                              7 /u01/app/oracle/oradata/LIGHT/lillo02.dbf                    .009765625 NO
INTRA                              8 /u01/app/oracle/oradata/LIGHT/intra02.dbf                    .024414063 NO
INTRA                              9 /u01/app/oracle/oradata/LIGHT/intra01.dbf                     .01953125 NO

9 rows selected.


TABLESPACE_NAME              FILE_ID FILE_NAME                                                       SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
TEMP                               1 /u01/app/oracle/oradata/LIGHT/temp01.dbf                      .01953125 YES


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


    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE


NAME
------------------------------------------------------------
/u01/app/oracle/oradata/LIGHT/control01.ctl
/u01/app/oracle/fast_recovery_area/LIGHT/control02.ctl

17:29:42 SYS@LIGHT>show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileLIGHT.ora
17:29:47 SYS@LIGHT>show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
17:30:04 SYS@LIGHT>show parameter back

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/lig
                                                 ht/LIGHT/trace
backup_tape_io_slaves                boolean     FALSE
db_flashback_retention_target        integer     1440
fast_start_parallel_rollback         string      LOW
rollback_segments                    string
transactions_per_rollback_segment    integer     5
17:31:00 SYS@LIGHT>show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
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


DB_SIZE_GB
----------
1.36328125


Step:-2. Target Server Setup and Precheck.


[grid@ora11gr2asm ~]$ ps -ef|grep pmon
grid      5566     1  0 17:06 ?        00:00:00 asm_pmon_+ASM
oracle    6016  5649  0 17:19 pts/2    00:00:00 grep pmon
[grid@ora11gr2asm ~]$ ps -ef|grep tns
grid      5470     1  0 17:05 ?        00:00:00 /u01/app/grid/product/11.2.0.4/grid_1/bin/tnslsnr LISTENER -inherit
oracle    6299  6136  0 17:49 pts/2    00:00:00 grep tns
[grid@ora11gr2asm ~]$



SQL> set lines 180 pages 180;
select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB,(FREE_MB/TOTAL_MB)*100 "%FREE" from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB    FREE_MB      %FREE
------------ ------------------------------ ----------- ---------- ---------- ----------
           1 DATA                           CONNECTED        81908      78124 95.3801826
           2 FRA                            CONNECTED        81908      81356 99.3260732

SQL> set lines 180 pages 180;
col PATH for A30;
select NAME,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH from v$asm_disk;

NAME                           GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
------------------------------ ------------ ----------- ------- ------------ ------- -------- ------------------------------
DISK1                                     1           1 CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK1
DISK2                                     1           0 CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK2
DISK3                                     2           0 CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK3
DISK4                                     2           1 CACHED  MEMBER       ONLINE  NORMAL   ORCL:DISK4



[grid@ora11gr2asm ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576     81908    78124                0           39062              0             N  DATA/
MOUNTED  NORMAL  N         512   4096  1048576     81908    81356                0           40678              0             N  FRA/
ASMCMD> ls
DATA/
FRA/
ASMCMD> mkdir DATA/LIGHT
ASMCMD> mkdir FRA/LIGHT
ASMCMD>


Step:-3. Make entry in /etc/hosts in both servers for connectivity.


[root@servera ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.72.179  servera.oracle.com      servera
192.168.72.230  serverb.oracle.com      serverb
192.168.72.189  ora11gr2asm.localdomain ora11gr2asm
[root@servera ~]#



[root@ora11gr2asm ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.72.189  ora11gr2asm.localdomain ora11gr2asm
192.168.72.179  servera.oracle.com      servera
[root@ora11gr2asm ~]#


Step:-4. Backup the pfile,password file and transfer to ASM server ORACLE_HOME/dbs location.


[oracle@servera Backup]$  cd $ORACLE_HOME/dbs
[oracle@servera dbs]$ sq

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 18:09:08 2018

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

18:09:08 SYS@LIGHT>create pfile from spfile;

File created.

18:09:14 SYS@LIGHT>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@servera dbs]$ ls -ltr initLIGHT.ora
-rw-r--r-- 1 oracle oinstall 899 Apr 19 18:09 initLIGHT.ora
[oracle@servera dbs]$ scp initLIGHT.ora oracle@ora11gr2asm:/u02/app/oracle/product/11.2.0.4/db_1/dbs/.
oracle@ora11gr2asm's password:
initLIGHT.ora                                                                                                               100%  899     0.9KB/s   00:00
[oracle@servera dbs]$ scp orapwLIGHT oracle@ora11gr2asm:/u02/app/oracle/product/11.2.0.4/db_1/dbs/.
oracle@ora11gr2asm's password:
orapwLIGHT                                                                                                                  100% 1536     1.5KB/s   00:00
[oracle@servera dbs]$


Step:-5. Take full backup of  Non-ASM database and transfer to ASM server.


[oracle@servera Backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 19 18:06:53 2018

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

connected to target database: LIGHT (DBID=722849799)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name LIGHT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_LIGHT.f'; # default

RMAN> run {
sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
set command id to 'DBOnlineBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag DB_FULL format '/mnt/hgfs/Backup/%d_%T_%s_%p_FULL.bkp' ;
sql 'alter system archive log current';
backup tag DB_ARCHIVE format '/mnt/hgfs/Backup/%d_%T_%s_%p_ARCHIVE.bkp' archivelog all;
backup tag DB_CONTROL current controlfile format '/mnt/hgfs/Backup/%d_%T_%s_%p_CONTROL.bkp';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>

sql statement: alter system archive log current

sql statement: alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''

executing command: SET COMMAND ID

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

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

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

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

Starting backup at 19-APR-2018 18:07:03
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/LIGHT/system01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/LIGHT/lillo02.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/LIGHT/users01.dbf
channel c1: starting piece 1 at 19-APR-2018 18:07:03
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/LIGHT/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/LIGHT/intra02.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/LIGHT/intra01.dbf
channel c2: starting piece 1 at 19-APR-2018 18:07:04
channel c3: starting compressed full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/LIGHT/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/LIGHT/hello01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/LIGHT/lillo01.dbf
channel c3: starting piece 1 at 19-APR-2018 18:07:05
channel c4: starting compressed full datafile backup set
channel c4: specifying datafile(s) in backup set
channel c3: finished piece 1 at 19-APR-2018 18:07:06
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_131_1_FULL.bkp tag=DB_FULL comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c3: starting compressed full datafile backup set
channel c3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c3: starting piece 1 at 19-APR-2018 18:07:07
including current control file in backup set
channel c4: starting piece 1 at 19-APR-2018 18:07:07
channel c3: finished piece 1 at 19-APR-2018 18:07:08
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_133_1_FULL.bkp tag=DB_FULL comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c4: finished piece 1 at 19-APR-2018 18:07:08
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_132_1_FULL.bkp tag=DB_FULL comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 19-APR-2018 18:07:42
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_130_1_FULL.bkp tag=DB_FULL comment=NONE
channel c2: backup set complete, elapsed time: 00:00:38
channel c1: finished piece 1 at 19-APR-2018 18:08:12
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_129_1_FULL.bkp tag=DB_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:01:09
Finished backup at 19-APR-2018 18:08:12

sql statement: alter system archive log current

Starting backup at 19-APR-2018 18:08:12
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=78 RECID=77 STAMP=973879128
channel c1: starting piece 1 at 19-APR-2018 18:08:13
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=79 RECID=78 STAMP=973879129
input archived log thread=1 sequence=80 RECID=79 STAMP=973879248
input archived log thread=1 sequence=81 RECID=80 STAMP=973879318
channel c2: starting piece 1 at 19-APR-2018 18:08:13
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=82 RECID=81 STAMP=973879318
input archived log thread=1 sequence=83 RECID=82 STAMP=973879407
input archived log thread=1 sequence=84 RECID=83 STAMP=973879476
channel c3: starting piece 1 at 19-APR-2018 18:08:13
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=1 sequence=85 RECID=84 STAMP=973879476
input archived log thread=1 sequence=86 RECID=85 STAMP=973879622
input archived log thread=1 sequence=87 RECID=86 STAMP=973879692
channel c4: starting piece 1 at 19-APR-2018 18:08:13
channel c1: finished piece 1 at 19-APR-2018 18:08:13
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_134_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=88 RECID=87 STAMP=973879692
channel c1: starting piece 1 at 19-APR-2018 18:08:13
channel c2: finished piece 1 at 19-APR-2018 18:08:13
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_135_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c3: finished piece 1 at 19-APR-2018 18:08:13
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_136_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c3: backup set complete, elapsed time: 00:00:00
channel c4: finished piece 1 at 19-APR-2018 18:08:13
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_137_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c4: backup set complete, elapsed time: 00:00:00
channel c1: finished piece 1 at 19-APR-2018 18:08:13
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_138_1_ARCHIVE.bkp tag=DB_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
Finished backup at 19-APR-2018 18:08:13

Starting backup at 19-APR-2018 18:08:13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 19-APR-2018 18:08:15
channel c1: finished piece 1 at 19-APR-2018 18:08:16
piece handle=/mnt/hgfs/Backup/LIGHT_20180419_139_1_CONTROL.bkp tag=DB_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-2018 18:08:16

released channel: c1

released channel: c2

released channel: c3

released channel: c4

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
69      B  F  A DISK        19-APR-2018 18:07:05 1       1       YES        DB_FULL
70      B  F  A DISK        19-APR-2018 18:07:07 1       1       YES        DB_FULL
71      B  F  A DISK        19-APR-2018 18:07:07 1       1       YES        DB_FULL
72      B  F  A DISK        19-APR-2018 18:07:39 1       1       YES        DB_FULL
73      B  F  A DISK        19-APR-2018 18:08:10 1       1       YES        DB_FULL
74      B  A  A DISK        19-APR-2018 18:08:13 1       1       NO         DB_ARCHIVE
75      B  A  A DISK        19-APR-2018 18:08:13 1       1       NO         DB_ARCHIVE
76      B  A  A DISK        19-APR-2018 18:08:13 1       1       NO         DB_ARCHIVE
77      B  A  A DISK        19-APR-2018 18:08:13 1       1       NO         DB_ARCHIVE
78      B  A  A DISK        19-APR-2018 18:08:13 1       1       NO         DB_ARCHIVE
79      B  F  A DISK        19-APR-2018 18:08:16 1       1       NO         DB_CONTROL

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
69      Full    1.42M      DISK        00:00:01     19-APR-2018 18:07:05
        BP Key: 69   Status: AVAILABLE  Compressed: YES  Tag: DB_FULL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_131_1_FULL.bkp
  List of Datafiles in backup set 69
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3       Full 983178     19-APR-2018 18:07:05 /u01/app/oracle/oradata/LIGHT/undotbs01.dbf
  5       Full 983178     19-APR-2018 18:07:05 /u01/app/oracle/oradata/LIGHT/hello01.dbf
  6       Full 983178     19-APR-2018 18:07:05 /u01/app/oracle/oradata/LIGHT/lillo01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
70      Full    1.06M      DISK        00:00:02     19-APR-2018 18:07:07
        BP Key: 70   Status: AVAILABLE  Compressed: YES  Tag: DB_FULL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_132_1_FULL.bkp
  Control File Included: Ckp SCN: 983179       Ckp time: 19-APR-2018 18:07:05

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
71      Full    80.00K     DISK        00:00:00     19-APR-2018 18:07:07
        BP Key: 71   Status: AVAILABLE  Compressed: YES  Tag: DB_FULL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_133_1_FULL.bkp
  SPFILE Included: Modification time: 27-MAR-2018 05:55:50
  SPFILE db_unique_name: LIGHT

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
72      Full    71.80M     DISK        00:00:35     19-APR-2018 18:07:39
        BP Key: 72   Status: AVAILABLE  Compressed: YES  Tag: DB_FULL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_130_1_FULL.bkp
  List of Datafiles in backup set 72
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2       Full 983177     19-APR-2018 18:07:04 /u01/app/oracle/oradata/LIGHT/sysaux01.dbf
  8       Full 983177     19-APR-2018 18:07:04 /u01/app/oracle/oradata/LIGHT/intra02.dbf
  9       Full 983177     19-APR-2018 18:07:04 /u01/app/oracle/oradata/LIGHT/intra01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
73      Full    193.26M    DISK        00:01:07     19-APR-2018 18:08:10
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: DB_FULL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_129_1_FULL.bkp
  List of Datafiles in backup set 73
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1       Full 983176     19-APR-2018 18:07:03 /u01/app/oracle/oradata/LIGHT/system01.dbf
  4       Full 983176     19-APR-2018 18:07:03 /u01/app/oracle/oradata/LIGHT/users01.dbf
  7       Full 983176     19-APR-2018 18:07:03 /u01/app/oracle/oradata/LIGHT/lillo02.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
74      99.50K     DISK        00:00:01     19-APR-2018 18:08:13
        BP Key: 74   Status: AVAILABLE  Compressed: NO  Tag: DB_ARCHIVE
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_134_1_ARCHIVE.bkp

  List of Archived Logs in backup set 74
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    78      981979     19-APR-2018 17:55:13 982166     19-APR-2018 17:58:48

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
75      199.50K    DISK        00:00:00     19-APR-2018 18:08:13
        BP Key: 75   Status: AVAILABLE  Compressed: NO  Tag: DB_ARCHIVE
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_135_1_ARCHIVE.bkp

  List of Archived Logs in backup set 75
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    79      982166     19-APR-2018 17:58:48 982174     19-APR-2018 17:58:48
  1    80      982174     19-APR-2018 17:58:48 982369     19-APR-2018 18:00:47
  1    81      982369     19-APR-2018 18:00:47 982659     19-APR-2018 18:01:58

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
76      4.50K      DISK        00:00:00     19-APR-2018 18:08:13
        BP Key: 76   Status: AVAILABLE  Compressed: NO  Tag: DB_ARCHIVE
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_136_1_ARCHIVE.bkp

  List of Archived Logs in backup set 76
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    82      982659     19-APR-2018 18:01:58 982667     19-APR-2018 18:01:58
  1    83      982667     19-APR-2018 18:01:58 982833     19-APR-2018 18:03:26
  1    84      982833     19-APR-2018 18:03:26 982930     19-APR-2018 18:04:36

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
77      7.50K      DISK        00:00:00     19-APR-2018 18:08:13
        BP Key: 77   Status: AVAILABLE  Compressed: NO  Tag: DB_ARCHIVE
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_137_1_ARCHIVE.bkp

  List of Archived Logs in backup set 77
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    85      982930     19-APR-2018 18:04:36 982938     19-APR-2018 18:04:36
  1    86      982938     19-APR-2018 18:04:36 983127     19-APR-2018 18:07:02
  1    87      983127     19-APR-2018 18:07:02 983219     19-APR-2018 18:08:12

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
78      2.00K      DISK        00:00:00     19-APR-2018 18:08:13
        BP Key: 78   Status: AVAILABLE  Compressed: NO  Tag: DB_ARCHIVE
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_138_1_ARCHIVE.bkp

  List of Archived Logs in backup set 78
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    88      983219     19-APR-2018 18:08:12 983227     19-APR-2018 18:08:12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
79      Full    9.61M      DISK        00:00:02     19-APR-2018 18:08:16
        BP Key: 79   Status: AVAILABLE  Compressed: NO  Tag: DB_CONTROL
        Piece Name: /mnt/hgfs/Backup/LIGHT_20180419_139_1_CONTROL.bkp
  Control File Included: Ckp SCN: 983243       Ckp time: 19-APR-2018 18:08:14

RMAN> exit


Recovery Manager complete.
[oracle@servera Backup]$ pwd
/mnt/hgfs/Backup
[oracle@servera Backup]$ ls -ltr
total 284280
-rwxrwxrwx 1 root root   1499136 Apr 19 12:40 LIGHT_20180419_131_1_FULL.bkp
-rwxrwxrwx 1 root root   1130496 Apr 19 12:40 LIGHT_20180419_132_1_FULL.bkp
-rwxrwxrwx 1 root root     98304 Apr 19 12:40 LIGHT_20180419_133_1_FULL.bkp
-rwxrwxrwx 1 root root  75300864 Apr 19 12:41 LIGHT_20180419_130_1_FULL.bkp
-rwxrwxrwx 1 root root 202653696 Apr 19 12:41 LIGHT_20180419_129_1_FULL.bkp
-rwxrwxrwx 1 root root    102400 Apr 19 12:41 LIGHT_20180419_134_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root    204800 Apr 19 12:41 LIGHT_20180419_135_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      5120 Apr 19 12:41 LIGHT_20180419_136_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      8192 Apr 19 12:41 LIGHT_20180419_137_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      2560 Apr 19 12:41 LIGHT_20180419_138_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root  10092544 Apr 19 12:41 LIGHT_20180419_139_1_CONTROL.bkp
[oracle@servera Backup]$



Note:- As "/mnt/hgfs/Backup/" is a shared drve I didn't copy these backup files to ASM server. This location is also visible in ASM server.


Step:-6. Make entry in /etc/orarab and set the sid and home.


[oracle@ora11gr2asm ~]$ cat /etc/oratab
#Backup file is  /u01/app/grid/product/11.2.0.4/grid_1/srvm/admin/oratab.bak.ora11gr2asm line added by Agent
#



# 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.
#
#
+ASM:/u01/app/grid/product/11.2.0.4/grid_1:N
DB11G:/u02/app/oracle/product/11.2.0.4/db_1:N           # line added by Agent
LIGHT:/u02/app/oracle/product/11.2.0.4/db_1:N
[oracle@ora11gr2asm ~]$
[oracle@ora11gr2asm ~]$ . oraenv
ORACLE_SID = [DB11G] ? LIGHT
The Oracle base remains unchanged with value /u02/app/oracle
[oracle@ora11gr2asm ~]$
[oracle@ora11gr2asm ~]$ cd $ORACLE_HOME/dbs
[oracle@ora11gr2asm dbs]$ pwd
/u02/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@ora11gr2asm dbs]$ ls -ltr
total 24
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-rw---- 1 oracle asmadmin 1544 Sep  2  2016 hc_DB11G.dat
-rw-r----- 1 oracle asmadmin   24 Sep  2  2016 lkDB11G
-rw-r----- 1 oracle oinstall   37 Sep  2  2016 initDB11G.ora
-rw-r----- 1 oracle oinstall 1536 Sep  2  2016 orapwDB11G
-rw-r--r-- 1 oracle oinstall  899 Apr 19 17:48 initLIGHT.ora
-rw-r----- 1 oracle oinstall 1536 Apr 19 18:00 orapwLIGHT
[oracle@ora11gr2asm dbs]$


Step:-7. Edit the pfile with required chnages using ASM file system.


[oracle@ora11gr2asm dbs]$ vi initLIGHT.ora
[oracle@ora11gr2asm dbs]$ cat initLIGHT.ora
LIGHT.__db_cache_size=654311424
LIGHT.__java_pool_size=16777216
LIGHT.__large_pool_size=33554432
LIGHT.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
LIGHT.__pga_aggregate_target=671088640
LIGHT.__sga_target=989855744
LIGHT.__shared_io_pool_size=0
LIGHT.__shared_pool_size=234881024
LIGHT.__streams_pool_size=33554432
*.audit_file_dest='/u02/app/oracle/admin/LIGHT/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/LIGHT/control01.ctl','+FRA/LIGHT/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='LIGHT'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=LIGHTXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/oradata/LIGHT/','+DATA/LIGHT/'
log_file_name_convert='/u01/app/oracle/oradata/LIGHT/','+FRA/LIGHT/'
[oracle@ora11gr2asm dbs]$


[oracle@ora11gr2asm dbs]$ mkdir -p /u02/app/oracle/admin/LIGHT/adump



Step:-8. Make static listener in ASM server.


[grid@ora11gr2asm ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2018 17:58:44

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

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                19-APR-2018 17:05:23
Uptime                    0 days 0 hr. 53 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0.4/grid_1/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ora11gr2asm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gr2asm.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "LIGHT" has 1 instance(s).
  Instance "LIGHT", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[grid@ora11gr2asm ~]$
[grid@ora11gr2asm ~]$ cd /u01/app/grid/product/11.2.0.4/grid_1/network/admin/
[grid@ora11gr2asm admin]$ pwd
/u01/app/grid/product/11.2.0.4/grid_1/network/admin
[grid@ora11gr2asm admin]$ ls -ltr
total 20
-rw-r--r-- 1 grid oinstall  381 Dec 17  2012 shrept.lst
drwxr-xr-x 2 grid oinstall 4096 Sep  2  2016 samples
-rw-r--r-- 1 grid oinstall  219 Sep  2  2016 sqlnet.ora
-rw-r--r-- 1 grid oinstall  384 Sep  2  2016 listener.ora.bak.ora11gr2asm
-rw-r--r-- 1 grid oinstall  450 Sep  2  2016 listener.ora
[grid@ora11gr2asm admin]$ vi listener.ora
[grid@ora11gr2asm admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/grid/product/11.2.0.4/grid_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gr2asm.localdomain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = LIGHT)
      (ORACLE_HOME = /u02/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = LIGHT)
    )
  )

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
[grid@ora11gr2asm admin]$
[grid@ora11gr2asm admin]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2018 18:00:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@ora11gr2asm admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-APR-2018 18:00:19

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

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                19-APR-2018 17:05:23
Uptime                    0 days 0 hr. 54 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0.4/grid_1/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ora11gr2asm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gr2asm.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "LIGHT" has 2 instance(s).
  Instance "LIGHT", status UNKNOWN, has 1 handler(s) for this service...
  Instance "LIGHT", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[grid@ora11gr2asm admin]$ 


Step:-9. Make tns entry for both the database (ASM db and Non-ASM db).


[oracle@ora11gr2asm ~]$ cd /u02/app/oracle/product/11.2.0.4/db_1/network/admin/
[oracle@ora11gr2asm admin]$ pwd
/u02/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@ora11gr2asm admin]$ ls -ltr
total 12
-rw-r--r-- 1 oracle oinstall  381 Dec 17  2012 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Sep  2  2016 samples
-rw-r----- 1 oracle oinstall  342 Sep  2  2016 tnsnames.ora
[oracle@ora11gr2asm admin]$ vi tnsnames.ora
[oracle@ora11gr2asm admin]$
[oracle@ora11gr2asm admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u02/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gr2asm.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G)
    )
  )

LIGHT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gr2asm.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LIGHT)
    )
  )

LIGHT_OLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = servera.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LIGHT)
    )
  )
[oracle@ora11gr2asm admin]$



[oracle@ora11gr2asm admin]$ tnsping LIGHT

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-APR-2018 18:06:19

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 = ora11gr2asm.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LIGHT)))
OK (20 msec)
[oracle@ora11gr2asm admin]$



[oracle@ora11gr2asm admin]$ tnsping LIGHT_OLD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-APR-2018 18:06:23

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 = servera.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LIGHT)))
OK (40 msec)
[oracle@ora11gr2asm admin]$


Step:-10. Make spfile from the pfile and modify the pfile accordingly.


[oracle@ora11gr2asm dbs]$ . oraenv
ORACLE_SID = [LIGHT] ?
The Oracle base remains unchanged with value /u02/app/oracle
[oracle@ora11gr2asm dbs]$
[oracle@ora11gr2asm dbs]$ sq

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 17:55:41 2018

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

Connected to an idle instance.

17:55:52 SYS@LIGHT>create spfile='+DATA/LIGHT/spfileLIGHT.ora' from pfile;

File created.

17:56:41 SYS@LIGHT>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11gr2asm dbs]$ ls
hc_DB11G.dat  hc_LIGHT.dat  initDB11G.ora  initLIGHT.ora  init.ora  lkDB11G  orapwDB11G
[oracle@ora11gr2asm dbs]$ cp initLIGHT.ora initLIGHT.ora_bkp
[oracle@ora11gr2asm dbs]$ vi initLIGHT.ora
[oracle@ora11gr2asm dbs]$ cat initLIGHT.ora
spfile='+DATA/LIGHT/spfileLIGHT.ora'
[oracle@ora11gr2asm dbs]$


Step:-11. Start the ASM database in nomount mode.


[oracle@ora11gr2asm dbs]$ . oraenv
ORACLE_SID = [LIGHT] ?
The Oracle base remains unchanged with value /u02/app/oracle
[oracle@ora11gr2asm dbs]$ sq

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 17:57:16 2018

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

Connected to an idle instance.

17:57:16 SYS@LIGHT>startup nomount
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
17:57:22 SYS@LIGHT>show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string      /u01/app/oracle/oradata/LIGHT/
                                                 , +DATA/LIGHT/
db_name                              string      LIGHT
db_unique_name                       string      LIGHT
global_names                         boolean     FALSE
instance_name                        string      LIGHT
lock_name_space                      string
log_file_name_convert                string      /u01/app/oracle/oradata/LIGHT/
                                                 , +FRA/LIGHT/
processor_group_name                 string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      LIGHT
17:57:25 SYS@LIGHT>show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/light/spfilelight.ora
17:57:32 SYS@LIGHT>


Step:-12. Connect with rman prompt with target and auxiliary database and start duplicate.


[oracle@ora11gr2asm dbs]$ cd /mnt/hgfs/Backup/
[oracle@ora11gr2asm Backup]$ pwd
/mnt/hgfs/Backup
[oracle@ora11gr2asm Backup]$ ls -ltr
total 284280
-rwxrwxrwx 1 root root   1499136 Apr 19 12:40 LIGHT_20180419_131_1_FULL.bkp
-rwxrwxrwx 1 root root   1130496 Apr 19 12:40 LIGHT_20180419_132_1_FULL.bkp
-rwxrwxrwx 1 root root     98304 Apr 19 12:40 LIGHT_20180419_133_1_FULL.bkp
-rwxrwxrwx 1 root root  75300864 Apr 19 12:41 LIGHT_20180419_130_1_FULL.bkp
-rwxrwxrwx 1 root root 202653696 Apr 19 12:41 LIGHT_20180419_129_1_FULL.bkp
-rwxrwxrwx 1 root root    102400 Apr 19 12:41 LIGHT_20180419_134_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root    204800 Apr 19 12:41 LIGHT_20180419_135_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      5120 Apr 19 12:41 LIGHT_20180419_136_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      8192 Apr 19 12:41 LIGHT_20180419_137_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root      2560 Apr 19 12:41 LIGHT_20180419_138_1_ARCHIVE.bkp
-rwxrwxrwx 1 root root  10092544 Apr 19 12:41 LIGHT_20180419_139_1_CONTROL.bkp
[oracle@ora11gr2asm Backup]$


[oracle@ora11gr2asm Backup]$ rman target sys/password@LIGHT_OLD auxiliary sys/password@LIGHT

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 19 18:07:07 2018

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

connected to target database: LIGHT (DBID=722849799)
connected to auxiliary database: LIGHT (not mounted)

RMAN> duplicate target database to 'LIGHT';

Starting Duplicate Db at 19-APR-2018 18:07:47
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''LIGHT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''LIGHT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     221331456 bytes

Fixed Size                     2251856 bytes
Variable Size                163578800 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5169152 bytes

Starting restore at 19-APR-2018 18:07:56
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_139_1_CONTROL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_139_1_CONTROL.bkp tag=DB_CONTROL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
output file name=+DATA/light/control01.ctl
output file name=+FRA/light/control02.ctl
Finished restore at 19-APR-2018 18:08:12

database mounted

contents of Memory Script:
{
   set until scn  983227;
   set newname for datafile  1 to
 "+DATA/light/system01.dbf";
   set newname for datafile  2 to
 "+DATA/light/sysaux01.dbf";
   set newname for datafile  3 to
 "+DATA/light/undotbs01.dbf";
   set newname for datafile  4 to
 "+DATA/light/users01.dbf";
   set newname for datafile  5 to
 "+DATA/light/hello01.dbf";
   set newname for datafile  6 to
 "+DATA/light/lillo01.dbf";
   set newname for datafile  7 to
 "+DATA/light/lillo02.dbf";
   set newname for datafile  8 to
 "+DATA/light/intra02.dbf";
   set newname for datafile  9 to
 "+DATA/light/intra01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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 restore at 19-APR-2018 18:08:18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA/light/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA/light/hello01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA/light/lillo01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_131_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_131_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA/light/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA/light/intra02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA/light/intra01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_130_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_130_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA/light/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA/light/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA/light/lillo02.dbf
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_129_1_FULL.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_129_1_FULL.bkp tag=DB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 19-APR-2018 18:10:26

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=973879826 file name=+DATA/light/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=973879827 file name=+DATA/light/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=973879827 file name=+DATA/light/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=973879827 file name=+DATA/light/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=973879827 file name=+DATA/light/hello01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=973879827 file name=+DATA/light/lillo01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=973879827 file name=+DATA/light/lillo02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=973879827 file name=+DATA/light/intra02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=973879827 file name=+DATA/light/intra01.dbf

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

executing command: SET until clause

Starting recover at 19-APR-2018 18:10:29
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=87
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_137_1_ARCHIVE.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_137_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/light/archivelog/2018_04_19/thread_1_seq_87.262.973879833 thread=1 sequence=87
channel clone_default: deleting archived log(s)
archived log file name=+FRA/light/archivelog/2018_04_19/thread_1_seq_87.262.973879833 RECID=88 STAMP=973879833
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=88
channel ORA_AUX_DISK_1: reading from backup piece /mnt/hgfs/Backup/LIGHT_20180419_138_1_ARCHIVE.bkp
channel ORA_AUX_DISK_1: piece handle=/mnt/hgfs/Backup/LIGHT_20180419_138_1_ARCHIVE.bkp tag=DB_ARCHIVE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=+FRA/light/archivelog/2018_04_19/thread_1_seq_88.262.973879835 thread=1 sequence=88
channel clone_default: deleting archived log(s)
archived log file name=+FRA/light/archivelog/2018_04_19/thread_1_seq_88.262.973879835 RECID=89 STAMP=973879838
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-2018 18:10:42
Oracle instance started

Total System Global Area     221331456 bytes

Fixed Size                     2251856 bytes
Variable Size                163578800 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5169152 bytes

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

Fixed Size                     2251856 bytes
Variable Size                163578800 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5169152 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "LIGHT" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+FRA/light/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+FRA/light/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+FRA/light/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/light/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA/light/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/light/sysaux01.dbf",
 "+DATA/light/undotbs01.dbf",
 "+DATA/light/users01.dbf",
 "+DATA/light/hello01.dbf",
 "+DATA/light/lillo01.dbf",
 "+DATA/light/lillo02.dbf",
 "+DATA/light/intra02.dbf",
 "+DATA/light/intra01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/light/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=+DATA/light/sysaux01.dbf RECID=1 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/undotbs01.dbf RECID=2 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/users01.dbf RECID=3 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/hello01.dbf RECID=4 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/lillo01.dbf RECID=5 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/lillo02.dbf RECID=6 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/intra02.dbf RECID=7 STAMP=973879860
cataloged datafile copy
datafile copy file name=+DATA/light/intra01.dbf RECID=8 STAMP=973879860

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=973879860 file name=+DATA/light/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=973879860 file name=+DATA/light/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=973879860 file name=+DATA/light/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=973879860 file name=+DATA/light/hello01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=973879860 file name=+DATA/light/lillo01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=973879860 file name=+DATA/light/lillo02.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=973879860 file name=+DATA/light/intra02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=973879860 file name=+DATA/light/intra01.dbf

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

database opened
Finished Duplicate Db at 19-APR-2018 18:11:35

RMAN>  exit


Recovery Manager complete.
[oracle@ora11gr2asm Backup]$


Step:-13. Add the database to ASM configuration using srvctl and start the db using srvctl.


[oracle@ora11gr2asm ~]$ srvctl add database -d LIGHT -o /u02/app/oracle/product/11.2.0.4/db_1 -p +DATA/light/spfilelight.ora -r primary -s OPEN -t IMMEDIATE -n LIGHT -i LIGHT -a DATA,FRA


[oracle@ora11gr2asm ~]$ srvctl config database -d LIGHT
Database unique name: LIGHT
Database name: LIGHT
Oracle home: /u02/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DATA/light/spfilelight.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: LIGHT
Disk Groups: DATA,FRA
Services:
[oracle@ora11gr2asm ~]$

[oracle@ora11gr2asm ~]$ sq

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 18:24:22 2018

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

18:24:22 SYS@LIGHT>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
18:24:34 SYS@LIGHT>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@ora11gr2asm ~]$ srvctl status database -d LIGHT -v
Database is not running.
[oracle@ora11gr2asm ~]$ srvctl start database -d LIGHT
[oracle@ora11gr2asm ~]$
[oracle@ora11gr2asm ~]$ srvctl status database -d LIGHT -v
Database is running.
[oracle@ora11gr2asm ~]$ sq

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 18:25:24 2018

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

18:25:24 SYS@LIGHT>set lines 200 pages 200
col HOST_NAME for A20
select name,open_mode,log_mode,database_role from v$database;
SELECT instance_name,LOGINS,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM v$instance; 18:25:29 SYS@LIGHT>18:25:29 SYS@LIGHT>
NAME      OPEN_MODE            LOG_MODE     DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT     READ WRITE           ARCHIVELOG   PRIMARY

18:25:29 SYS@LIGHT>

INSTANCE_NAME    LOGINS     HOST_NAME            DB Startup Time
---------------- ---------- -------------------- --------------------
LIGHT            ALLOWED    ora11gr2asm.localdom 19-APR-2018 18:24:48
                            ain


18:25:29 SYS@LIGHT>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@ora11gr2asm ~]$



Step:-14. Check the final database configuration.


[oracle@ora11gr2asm ~]$ ps -ef|grep pmon
grid      5566     1  0 17:06 ?        00:00:00 asm_pmon_+ASM
oracle   13411     1  0 18:10 ?        00:00:00 ora_pmon_LIGHT
oracle   13565  6136  0 18:17 pts/2    00:00:00 grep pmon

[oracle@ora11gr2asm ~]$ ps -ef|grep tns
grid      5470     1  0 17:05 ?        00:00:00 /u01/app/grid/product/11.2.0.4/grid_1/bin/tnslsnr LISTENER -inherit
oracle   13567  6136  0 18:17 pts/2    00:00:00 grep tns
[oracle@ora11gr2asm ~]$


NAME      OPEN_MODE            LOG_MODE     DATABASE_ROLE
--------- -------------------- ------------ ----------------
LIGHT     READ WRITE           ARCHIVELOG   PRIMARY

18:12:34 SYS@LIGHT>

INSTANCE_NAME    LOGINS     HOST_NAME            DB Startup Time
---------------- ---------- -------------------- --------------------
LIGHT            ALLOWED    ora11gr2asm.localdom 19-APR-2018 18:10:52
                            ain


18:12:34 SYS@LIGHT>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

18:12:41 SYS@LIGHT>show parameter back

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u02/app/oracle/diag/rdbms/lig
                                                 ht/LIGHT/trace
backup_tape_io_slaves                boolean     FALSE
db_flashback_retention_target        integer     1440
fast_start_parallel_rollback         string      LOW
rollback_segments                    string
transactions_per_rollback_segment    integer     5

18:12:47 SYS@LIGHT>show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 4182M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

18:12:51 SYS@LIGHT>show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string


TABLESPACE_NAME                               size MB     free MB % used
---------------------------------------- ------------ ----------- ------
HELLO                                              30          29      3
INTRA                                              45          43      4
LILLO                                              36          34      5
USERS                                               5           4     26
UNDOTBS1                                           30          15     51
SYSAUX                                            510          29     94
SYSTEM                                            740           3     99
TEMP **TEMP**                                      20           0    100

8 rows selected.


TABLESPACE_NAME              FILE_ID FILE_NAME                                                       SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
SYSTEM                             1 +DATA/light/system01.dbf                                      .72265625 YES
SYSAUX                             2 +DATA/light/sysaux01.dbf                                     .498046875 YES
UNDOTBS1                           3 +DATA/light/undotbs01.dbf                                    .029296875 YES
USERS                              4 +DATA/light/users01.dbf                                      .004882813 YES
HELLO                              5 +DATA/light/hello01.dbf                                      .029296875 NO
LILLO                              6 +DATA/light/lillo01.dbf                                      .025390625 NO
LILLO                              7 +DATA/light/lillo02.dbf                                      .009765625 NO
INTRA                              8 +DATA/light/intra02.dbf                                      .024414063 NO
INTRA                              9 +DATA/light/intra01.dbf                                       .01953125 NO

9 rows selected.

TABLESPACE_NAME              FILE_ID FILE_NAME                                                       SIZE_GB AUT
------------------------- ---------- ------------------------------------------------------------ ---------- ---
TEMP                               1 +DATA/light/temp01.dbf                                        .01953125 YES


NAME
------------------------------------------------------------
+DATA/light/control01.ctl
+FRA/light/control02.ctl


MEMBER
------------------------------------------------------------
+FRA/light/redo03.log
+FRA/light/redo02.log
+FRA/light/redo01.log

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 ACTIVE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/light/spfilelight.ora






=========================== END ===========================



No comments:

Post a Comment