Search This Blog

Total Pageviews

Sunday, 17 April 2016

DUPLICATE DATABASE WITH RMAN BACKUP

Oracle DUPLICATE DATABASE WITH RMAN BACKUP ...

DUPLICATE DATABASE WITHOUT CONNECTING TO TARGET DATABASE ON SAME ASM disk group ..


If duplicating a database on the same host as the source database, then ensure that NOFILENAMECHECK is not set. Otherwise, RMAN can potentially overwrite and corrupt the target database data files, temp files, or online logs. 



cat /etc/oratab 

ora11g:/u01/app/oracle/product/11.2.0/db_1:N            # line added by Agent  ------ This is a orignal database 
ora11ga:/u01/app/oracle/product/11.2.0/db_1:N          # line added by Agent  ------ aim to create duplicate database on same +ASM disk group i.e. +DATA
+ASM:/u01/app/grid/product/12.2.0/grid:N                 # line added by Agent


ora11g database init file i.e. orignal database ...

[oracle@ora12c dbs]$ cat initora11g.ora
ora11g.__db_cache_size=503316480
ora11g.__java_pool_size=16777216
ora11g.__large_pool_size=33554432
ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=503316480
ora11g.__sga_target=754974720
ora11g.__shared_io_pool_size=0
ora11g.__shared_pool_size=184549376
ora11g.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/ora11g/controlfile/current.281.905721923','+DATA/ora11g/controlfile/current.282.905721925'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.memory_target=1258291200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/ora11g/adump'
*.log_archive_dest_1='location=+DATA'




creating  duplicate database name ora11ga

ora11ga database init file i.e. duplicate database ...


cat initora11ga.ora

ora11ga.__db_cache_size=503316480
ora11ga.__java_pool_size=16777216
ora11ga.__large_pool_size=33554432
ora11ga.__oracle_base='/u01/app/oracle'
ora11ga.__pga_aggregate_target=503316480
ora11ga.__sga_target=754974720
ora11ga.__shared_io_pool_size=0
ora11ga.__shared_pool_size=184549376
ora11ga.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11ga'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gaXDB)'
*.memory_target=1258291200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='+DATA/ORA11GA'
*.log_archive_dest_1='location=+DATA/ORA11GA'
*.db_unique_name='ora11ga'
*.db_file_name_convert  = ("+DATA/ORA11G","+DATA/ORA11GA")
*.log_file_name_convert = ("+DATA/ORA11G","+DATA/ORA11GA")
*.audit_file_dest='/u01/app/oracle/admin/ora11ga/adump'


On ASM create a new dir ..

ASMCMD [+DATA] > ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            Y    ORA11G/
PASSWORD  UNPROT  COARSE   JAN 09 18:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.900698615
ASMCMD [+DATA] > mkdir ORA11GA ------ create dir in ASM


set the environment for duplicate database  

[oracle@ora12c dbs]$ . oraenv
ORACLE_SID = [ora11g] ? ora11ga
The Oracle base remains unchanged with value /u01/app/oracle

start the database in nomount 

SQL> startup nomount pfile='initora11ga1.ora' ;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             788533224 bytes
Database Buffers          452984832 bytes
Redo Buffers                8892416 bytes


Rman backup location ...

[oracle@ora12c dbs]$ cd /u01/app/RmanBackup/
[oracle@ora12c RmanBackup]$ ls -ltr
total 4
drwxr-xr-x 2 oracle oinstall 4096 Mar  6 07:01 ORA11G
[oracle@ora12c RmanBackup]$ cd ORA11G/

[oracle@ora12c ORA11G]$ pwd
/u01/app/RmanBackup/ORA11G  <<<<<<--------

[oracle@ora12c ORA11G]$ ls -ltr
total 339416
-rw-r----- 1 oracle asmadmin 346447872 Mar  5 18:08 20160305_ora11g_6_1_905710001
-rw-r----- 1 oracle asmadmin   1097728 Mar  5 18:08 20160305_ora11g_7_1_905710117
-rw-r----- 1 oracle asmadmin      3584 Mar  5 18:08 20160305_ora11g_8_1_905710124


rman auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 16 21:16:27 2016

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

connected to auxiliary database: ORA11GA (not mounted)



RMAN> duplicate target database to ora11ga backup location '/u01/app/RmanBackup/ORA11G/'  nofilenamecheck ;

Starting Duplicate Db at 16-APR-16

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

sql statement: create spfile from memory

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/ora11ga/controlfile/current.312.909350245'', ''+DATA/ora11ga/controlfile/current.311.909350245'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORA11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORA11GA'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/RmanBackup/ORA11G/20160305_
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/ora11ga/controlfile/c                                                                                        et by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORA11G'' comment= ''Modified by RM

sql statement: alter system set  db_unique_name =  ''ORA11GA'' comment= ''Modifi

Oracle instance shut down

Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

Starting restore at 16-APR-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:10
output file name=+DATA/ora11ga/controlfile/current.312.909350245
output file name=+DATA/ora11ga/controlfile/current.311.909350245
Finished restore at 16-APR-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  760753;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   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

Starting restore at 16-APR-16
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 00001 to +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/ORA11G/20160305_ora11g_6_1_905710001
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/ORA11G/20160305_ora11g_6_1_905710001 tag=TAG20160305T180640
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:44
Finished restore at 16-APR-16

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

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=909350516 file name=+DATA/ora11ga/datafile/system.310.909350291
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=909350517 file name=+DATA/ora11ga/datafile/sysaux.295.909350291
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=909350517 file name=+DATA/ora11ga/datafile/undotbs1.294.909350291
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=909350518 file name=+DATA/ora11ga/datafile/users.293.909350291

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

executing command: SET until clause

Starting recover at 16-APR-16
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=68
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/ORA11G/20160305_ora11g_8_1_905710124
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/ORA11G/20160305_ora11g_8_1_905710124 tag=TAG20160305T180843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=+DATA/ora11ga/1_68_900708574.dbf thread=1 sequence=68
channel clone_default: deleting archived log(s)
archived log file name=+DATA/ora11ga/1_68_900708574.dbf RECID=1 STAMP=909350535
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-APR-16
Oracle instance started

Total System Global Area    1252663296 bytes

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes

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

Fixed Size                     2252824 bytes
Variable Size                805310440 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8892416 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA11GA" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA/ora11ga/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+DATA/ora11ga/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+DATA/ora11ga/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/ora11ga/datafile/system.310.909350291'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/ora11ga/datafile/sysaux.295.909350291",
 "+DATA/ora11ga/datafile/undotbs1.294.909350291",
 "+DATA/ora11ga/datafile/users.293.909350291";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/sysaux.295.909350291 RECID=1 STAMP=909350576
cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/undotbs1.294.909350291 RECID=2 STAMP=909350578
cataloged datafile copy
datafile copy file name=+DATA/ora11ga/datafile/users.293.909350291 RECID=3 STAMP=909350578

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=909350576 file name=+DATA/ora11ga/datafile/sysaux.295.909350291
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=909350578 file name=+DATA/ora11ga/datafile/undotbs1.294.909350291
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=909350578 file name=+DATA/ora11ga/datafile/users.293.909350291

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

database opened
Finished Duplicate Db at 16-APR-16

=======================================


Check the new database i.e. ora11ga


SQL> select * from dual;

D
-
X


show parameter spfile;

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

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/ora11ga
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



alter session set nls_date_format='dd-mm-yyyy HH24:mi';

SQL> select name,CREATED from v$database;

NAME      CREATED
--------- ----------------
ORA11GA   16-04-2016 21:22



SQL> select FILE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/ora11ga/datafile/system.310.909350291
+DATA/ora11ga/datafile/sysaux.295.909350291
+DATA/ora11ga/datafile/undotbs1.294.909350291
+DATA/ora11ga/datafile/users.293.909350291


set numf 999.99 linesize 200 pagesize 200
col FILE_NAME            for a50
col AUTOEXTENSIBLE for a15
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb ,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 Max_Gb FROM dba_temp_files
WHERE tablespace_name in (select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY') ;

TABLESPACE_NAME                FILE_NAME                                               GB AUTOEXTENSIBLE   MAX_GB
------------------------------ -------------------------------------------------- ------- --------------- -------
TEMP                           +DATA/ora11ga/tempfile/temp.304.909350621              .06 YES               32.00



select 
   group#, 
   members, 
   thread#, 
   sequence#, 
   bytes/1024/1024 mb, 
   archived, 
   status, 
   first_time
from  v$log
order by group#;

    GROUP#    MEMBERS    THREAD#  SEQUENCE#         MB ARC STATUS           FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ----------------
         1          1          1          1         50 NO  CURRENT          16-04-2016 21:23
         2          1          1          0         50 YES UNUSED
         3          1          1          0         50 YES UNUSED


col MEMBER                   for a50
col IS_RECOVERY_DEST_FILE for a25
SELECT * FROM V$LOGFILE;

  GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- -------------------------
         3         ONLINE  +DATA/ora11ga/redo03.log                           NO
         2         ONLINE  +DATA/ora11ga/redo02.log                           NO
         1         ONLINE  +DATA/ora11ga/redo01.log                           NO



Displays information about redo log files.

SET LINESIZE 200 pagesize 200
COLUMN member          FORMAT A30
COLUMN first_change#   FORMAT 99999999999999999999
COLUMN next_change#    FORMAT 99999999999999999999
SELECT l.thread#,
       lf.group#,
       lf.member,
       TRUNC(l.bytes/1024/1024) AS size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file AS rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
FROM   v$logfile lf, v$log l
where  l.group# = lf.group#
ORDER BY l.thread#,lf.group#, lf.member;


THREAD#  GROUP# MEMBER                         SIZE_MB STATUS           ARC TYPE    RDF SEQUENCE#         FIRST_CHANGE#          NEXT_CHANGE#
------- ------- ------------------------------ ------- ---------------- --- ------- --- --------- --------------------- ---------------------
   1.00    1.00 +DATA/ora11ga/redo01.log         50.00 INACTIVE         YES ONLINE  NO       1.00                760754                783792
   1.00    2.00 +DATA/ora11ga/redo02.log         50.00 CURRENT          NO  ONLINE  NO       2.00                783792       281474976710655
   1.00    3.00 +DATA/ora11ga/redo03.log         50.00 UNUSED           YES ONLINE  NO        .00                     0                     0


SET LINESIZE 80
SELECT GROUP#, BYTES, 'ONLINE'  AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;

    GROUP#      BYTES TYPE
---------- ---------- -------
         1   52428800 ONLINE
         2   52428800 ONLINE
         3   52428800 ONLINE

=====================================================
For file system !!!!!!!!!!!!!!!!!!!!!

[oracle@ora tmp]$ cat oraeed.txt
oraeed.__db_cache_size=360710144
oraeed.__java_pool_size=4194304
oraeed.__large_pool_size=4194304
oraeed.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oraeed.__pga_aggregate_target=184549376
oraeed.__sga_target=536870912
oraeed.__shared_io_pool_size=0
oraeed.__shared_pool_size=159383552
oraeed.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='oraee'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraeeXDB)'
*.open_cursors=300
*.pga_aggregate_target=183500800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/oraeed/adump'
*.control_files='/u01/app/oracle/oradata/oraeed/control01.ctl'
*.log_archive_dest_1='location=/u01/app/oracle/ArchiveEED'
*.db_file_name_convert  = ("/u01/app/oracle/oradata/oraee/oraee","/u01/app/oracle/oradata/oraeed")
*.log_file_name_convert = ("/u01/app/oracle/oradata/oraee/oraee","/u01/app/oracle/oradata/oraeed")
*.db_create_file_dest='/u01/app/oracle/oradata/oraeed'



export ORACLE_SID=oraeed



SQL> def

DEFINE _DATE           = "15-JAN-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oraeed" (CHAR)  <<<<<<<<<<---- Check Oracle SID
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Release 11.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)



SQL> create spfile from pfile='/tmp/oraeed.txt';


File created.


SQL> startup nomount ;

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2215064 bytes
Variable Size             167773032 bytes
Database Buffers          360710144 bytes
Redo Buffers                3764224 bytes
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production



[oracle@ora tmp]$ rman auxiliary /


Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 15 09:07:16 2017


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


connected to auxiliary database: ORAEE (not mounted)





RMAN> duplicate target database to oraeed backup location '/u01/app/oracle/RmanBackup/'  nofilenamecheck ;


Starting Duplicate Db at 15-JAN-17


contents of Memory Script:

{
   sql clone "alter system set  db_name =
 ''ORAEE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/oracle/RmanBackup/c-2924229127-20170115-03.bck';
   alter clone database mount;
}
executing Memory Script

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


Oracle instance shut down


Oracle instance started


Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

Variable Size                167773032 bytes
Database Buffers             360710144 bytes
Redo Buffers                   3764224 bytes

Starting restore at 15-JAN-17

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/oraeed/control01.ctl
Finished restore at 15-JAN-17

database mounted

released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:

{
   set until scn  975349;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/oraeed/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/oraeed/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/oraeed/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/oraeed/users01.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


Starting restore at 15-JAN-17

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 00001 to /u01/app/oracle/oradata/oraeed/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oraeed/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oraeed/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oraeed/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORAEE_20170115_6_1_FULL
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170115_6_1_FULL tag=ORCLEE_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 15-JAN-17

contents of Memory Script:

{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=933325748 file name=/u01/app/oracle/oradata/oraeed/users01.dbf

contents of Memory Script:

{
   set until scn  975349;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause


Starting recover at 15-JAN-17

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=5
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORAEE_20170115_8_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170115_8_1_ARCHIVE tag=ORCLEE_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=/u01/app/oracle/ArchiveEED/1_5_933321159.dbf thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEED/1_5_933321159.dbf RECID=1 STAMP=933325749
archived log file name=/u01/app/oracle/ArchiveEED/1_6_933321159.dbf thread=1 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEED/1_6_933321159.dbf RECID=2 STAMP=933325749
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-JAN-17

contents of Memory Script:

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

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

Variable Size                167773032 bytes
Database Buffers             360710144 bytes
Redo Buffers                   3764224 bytes

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


Oracle instance shut down


connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     534462464 bytes


Fixed Size                     2215064 bytes

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


contents of Memory Script:

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

executing command: SET NEWNAME


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


cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf RECID=1 STAMP=933325759
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf RECID=2 STAMP=933325759
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/oraeed/users01.dbf RECID=3 STAMP=933325759

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=933325759 file name=/u01/app/oracle/oradata/oraeed/users01.dbf

contents of Memory Script:

{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

Finished Duplicate Db at 15-JAN-17



====================


Check All datafile ....



COLUMN  today NOPRINT New_Value strToday

SELECT  TO_CHAR( SYSDATE, 'DD Mon YYYY HH24:MI:SS' ) today FROM DUAL;
COLUMN  DATABASE_NAME NOPRINT New_Value strDatabaseName
SELECT  'Data File Report (all physical files) '||HOST_NAME ||'-' || DB_UNIQUE_NAME AS DATABASE_NAME FROM V$Database,V$INSTANCE ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'File Report (all physical files)' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
SET ECHO OFF  FEEDBACK 6 HEADING  ON LINESIZE 200 PAGESIZE 500  TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL   ON  VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace      FORMAT a30                    HEADING 'Tablespace Name / File Class'
COLUMN filename        FORMAT a75                    HEADING 'Filename'
COLUMN filesize_mb     FORMAT 9,999,999,999,999      HEADING 'File Size MB'
COLUMN autoextensible  FORMAT a4                     HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999,999        HEADING 'Next'
COLUMN max_mb          FORMAT 999,999,999,999        HEADING 'Max MB'
BREAK ON report
COMPUTE sum OF filesize  ON report
SELECT /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM sys.dba_data_files d , v$datafile v
  , (SELECT value  FROM v$parameter  WHERE name = 'db_block_size') e
WHERE  (d.file_name = v.name)
UNION
SELECT
    d.tablespace_name                     tablespace 
  , d.file_name                           filename
  , d.bytes/1024/1024                     filesize_mb
  , d.autoextensible                      autoextensible
  , (d.increment_by * e.value)/1024/1024  increment_by
  , d.maxbytes/1024/1024                  max_mb
FROM  sys.dba_temp_files d
  , (SELECT value  FROM v$parameter   WHERE name = 'db_block_size') e
UNION
SELECT
    '[ ONLINE REDO LOG ]'
  , a.member
  , b.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , b.bytes/1024/1024
FROM  v$logfile a, v$log b
WHERE   a.group# = b.group#
UNION
SELECT
    '[ CONTROL FILE    ]'
  , a.name
  ,(select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  v$controlfile a
union
SELECT
    '[ BLOCK TRACKING]'||a.status
  , a.filename
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$BLOCK_CHANGE_TRACKING a
union 
SELECT
    '[ FLASHBACK DATABASE]'
  , a.name
  , a.bytes/1024/1024
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  V$FLASHBACK_DATABASE_LOGFILE a
union
SELECT
    '[ Spfile or Pfile]'||DECODE(value, NULL, 'PFILE', 'SPFILE')
  , a.DISPLAY_VALUE
  , TO_NUMBER(null)
  , null
  , TO_NUMBER(null)
  , TO_NUMBER(null)
FROM  sys.v_$parameter a WHERE name = 'spfile'
ORDER BY 1,2
/


______________________________________________________________________________________________________________________


                                                                                          15 Jan 2017 09:46:55

                                                                                    File Report (all physical files)
                                                                          Data File Report (all physical files) ora.singh.com-
______________________________________________________________________________________________________________________

Tablespace Name / File Class   Filename                                                                          File Size MB Auto             Next           Max MB

------------------------------ --------------------------------------------------------------------------- ------------------ ---- ---------------- ----------------
SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf                                                440 YES                10           32,768
SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf                                                670 YES                10           32,768
TEMP                           /u01/app/oracle/oradata/oraeed/temp01.dbf                                                   20 YES                 1           32,768
UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf                                                30 YES                 5           32,768
USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf                                                   5 YES                 1           32,768
[ BLOCK TRACKING]DISABLED
[ CONTROL FILE    ]            /u01/app/oracle/oradata/oraeed/control01.ctl                                                10
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo01.log                                                   50                                     50
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo02.log                                                   50                                     50
[ ONLINE REDO LOG ]            /u01/app/oracle/oradata/oraeed/redo03.log                                                   50                                     50
[ Spfile or Pfile]SPFILE       /u01/app/oracle/product/11.2.0/dbhome_3/dbs/spfileoraeed.ora

11 rows selected.





SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/ArchiveEED
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


SQL> alter system switch logfile;


System altered.


SQL> !ls -ltr /u01/app/oracle/ArchiveEED

total 1284
-rw-r----- 1 oracle oinstall 1312768 Jan 15 09:49 1_1_933325760.dbf


=====


backup 


backup as compressed backupset  incremental level 0 format '/dumps/VIHAAN8/rman/%T_@_%s_%p_%t'  database include current controlfile plus archivelog;


www
duplicate target database to VIHCDBD9 backup location '/dumps/VIHAAN8/rman/' pluggable database ANUJV,anujy  ;


show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 ANUJV                          READ WRITE NO
         5 ANUJY                          READ WRITE NO



=====


RMAN>
RMAN> 2> 3>
Starting Duplicate Db at 09-MAY-2024 08:27:32
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/09/2024 08:27:32
RMAN-05501: aborting duplication of target database
RMAN-05575: CONTROLFILE backup created before TO_DATE('25-JAN-2022 05:55:36','DD-MON-YYYY HH24:MI:SS') not found in /dumps/UGARY-ORIG/


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3       Incr 0  1.03M      DISK        00:00:01     25-JAN-2022 05:56:44
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20220125T055608
        Piece Name: /dumps/UGARY-ORIG/20220125_ugary_7_1_1094882203
  SPFILE Included: Modification time: 25-JAN-2022 05:55:03
  SPFILE db_unique_name: UGARY
  Control File Included: Ckp SCN: 445380       Ckp time: 25-JAN-2022 05:56:43


take time+1 from above !!!!!!

RMAN> run{
  DUPLICATE DATABASE TO ugaryd BACKUP LOCATION '/dumps/UGARY-ORIG/' nofilenamecheck until time "TO_DATE('25-JAN-2022 05:56:44','DD-MON-YYYY HH24:MI:SS')";
}


WWW


RMAN>
RMAN>
RMAN> startup nomount ;

connected to target database (not started)
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

RMAN>



RMAN> connect auxiliary /


RMAN> run{
  DUPLICATE DATABASE TO ugaryd BACKUP LOCATION '/dumps/UGARY-ORIG/' nofilenamecheck until time "TO_DATE('25-JAN-2022 05:56:44','DD-MON-YYYY HH24:MI:SS')";
}
2> 3>
Starting Duplicate Db at 09-MAY-2024 08:31:35

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/UGARYD/CONTROLFILE/current.4591.1168502707'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''UGARY'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''UGARYD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/dumps/UGARY-ORIG/20220125_ugary_7_1_1094882203';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/UGARYD/CONTROLFILE/current.4591.1168502707'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

Starting restore at 09-MAY-2024 08:32:04
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=297 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/UGARYD/CONTROLFILE/current.4591.1168502707
Finished restore at 09-MAY-2024 08:32:07

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=297 device type=DISK

contents of Memory Script:
{
   set until scn  445380;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-MAY-2024 08:32:12
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 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /dumps/UGARY-ORIG/20220125_ugary_6_1_1094882168

channel ORA_AUX_DISK_1: piece handle=/dumps/UGARY-ORIG/20220125_ugary_6_1_1094882168 tag=TAG20220125T055608
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 09-MAY-2024 08:32:57

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

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1168504377 file name=+DATA/UGARYD/DATAFILE/system.4563.1168504333
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=1168504377 file name=+DATA/UGARYD/DATAFILE/sysaux.4679.1168504333
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1168504377 file name=+DATA/UGARYD/DATAFILE/undotbs1.4572.1168504333

contents of Memory Script:
{
   set until time  "to_date('JAN 25 2022 05:56:44', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-MAY-2024 08:32:57
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=15
channel ORA_AUX_DISK_1: reading from backup piece /dumps/UGARY-ORIG/20220125_ugary_8_1_1094882205
channel ORA_AUX_DISK_1: piece handle=/dumps/UGARY-ORIG/20220125_ugary_8_1_1094882205 tag=TAG20220125T055645
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=+DATA/UGARYD/ARCHIVELOG/2024_05_09/thread_1_seq_15.4558.1168504379 thread=1 sequence=15
channel clone_default: deleting archived log(s)
archived log file name=+DATA/UGARYD/ARCHIVELOG/2024_05_09/thread_1_seq_15.4558.1168504379 RECID=1 STAMP=1168504379
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-MAY-2024 08:33:00
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UGARYD" RESETLOGS ARCHIVELOG
  MAXLOGFILES      5
  MAXLOGMEMBERS      3
  MAXDATAFILES      200
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 100 M ,
  GROUP   2  SIZE 100 M ,
  GROUP   3  SIZE 100 M
 DATAFILE
  '+DATA/UGARYD/DATAFILE/system.4563.1168504333'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/UGARYD/DATAFILE/sysaux.4679.1168504333",
 "+DATA/UGARYD/DATAFILE/undotbs1.4572.1168504333";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/UGARYD/DATAFILE/sysaux.4679.1168504333 RECID=1 STAMP=1168504408
cataloged datafile copy
datafile copy file name=+DATA/UGARYD/DATAFILE/undotbs1.4572.1168504333 RECID=2 STAMP=1168504408

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1168504408 file name=+DATA/UGARYD/DATAFILE/sysaux.4679.1168504333
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1168504408 file name=+DATA/UGARYD/DATAFILE/undotbs1.4572.1168504333

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

database opened
Finished Duplicate Db at 09-MAY-2024 08:33:30



*****************************************************************************************



ww


  List of Archived Logs in backup set 82
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    4       2570846    17-05-2024 18:41:01 2571746    17-05-2024 18:41:05   <<<<< '17-MAY-2024 18:40:00'
recovery will be done up to SCN 2569919
Media recovery start SCN is 2569919
Recovery must be done beyond SCN 2569919 to clear datafile fuzziness
Finished restore at 17-05-2024 18:41:21



rman auxiliary / | tee rman_`/bin/date +\%d\%m\%y:%H:%M:%S`.log

run{
  DUPLICATE DATABASE TO doradb BACKUP LOCATION '/u01/app/Rman/' nofilenamecheck until time "TO_DATE('17-MAY-2024 18:40:00','DD-MON-YYYY HH24:MI:SS')";
}
.
.
.
.
.


contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Finished Duplicate Db at 17-05-2024 22:33:16

RMAN>
RMAN>



================





sed -i 's/ugaryd/ugaryt/g' initugaryt.ora
cat initugaryt.ora
sed -i 's/UGARYD/UGARYT/g' initugaryt.ora


$rman

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Oct 23 07:39:04 2024

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

RMAN> connect auxiliary /

connected to auxiliary database: UGARYT (not mounted)

RMAN> DUPLICATE DATABASE TO ugaryt BACKUP LOCATION '/dumps/UGARYD/' ;

Starting Duplicate Db at 23-OCT-24

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

sql statement: create spfile from memory

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/UGARYT/CONTROLFILE/current.3838.1183102795'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''UGARYD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''UGARYT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/dumps/UGARYD/20241023_ugaryd_6_1_1183101561';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/UGARYT/CONTROLFILE/current.3838.1183102795'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

Starting restore at 23-OCT-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=297 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/UGARYT/CONTROLFILE/current.3838.1183102795
Finished restore at 23-OCT-24

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=297 device type=DISK

contents of Memory Script:
{
   set until scn  8342218;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-OCT-24
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 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /dumps/UGARYD/20241023_ugaryd_5_1_1183101526
channel ORA_AUX_DISK_1: piece handle=/dumps/UGARYD/20241023_ugaryd_5_1_1183101526 tag=TAG20241023T071846
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 23-OCT-24

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

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1183102866 file name=+DATA/UGARYT/DATAFILE/system.3834.1183102831
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=1183102866 file name=+DATA/UGARYT/DATAFILE/sysaux.3815.1183102831
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1183102866 file name=+DATA/UGARYT/DATAFILE/undotbs1.3823.1183102831

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

executing command: SET until clause

Starting recover at 23-OCT-24
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=146
channel ORA_AUX_DISK_1: reading from backup piece /dumps/UGARYD/20241023_ugaryd_7_1_1183101564
channel ORA_AUX_DISK_1: piece handle=/dumps/UGARYD/20241023_ugaryd_7_1_1183101564 tag=TAG20241023T071924
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=+DATA/UGARYT/ARCHIVELOG/2024_10_23/thread_1_seq_146.3830.1183102867 thread=1 sequence=146
channel clone_default: deleting archived log(s)
archived log file name=+DATA/UGARYT/ARCHIVELOG/2024_10_23/thread_1_seq_146.3830.1183102867 RECID=1 STAMP=1183102867
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-OCT-24
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3712904 bytes
Variable Size                687868024 bytes
Database Buffers            1442840576 bytes
Redo Buffers                  13062144 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UGARYT" RESETLOGS ARCHIVELOG
  MAXLOGFILES      5
  MAXLOGMEMBERS      3
  MAXDATAFILES      200
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 100 M ,
  GROUP   2  SIZE 100 M ,
  GROUP   3  SIZE 100 M
 DATAFILE
  '+DATA/UGARYT/DATAFILE/system.3834.1183102831'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/UGARYT/DATAFILE/sysaux.3815.1183102831",
 "+DATA/UGARYT/DATAFILE/undotbs1.3823.1183102831";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/UGARYT/DATAFILE/sysaux.3815.1183102831 RECID=1 STAMP=1183102894
cataloged datafile copy
datafile copy file name=+DATA/UGARYT/DATAFILE/undotbs1.3823.1183102831 RECID=2 STAMP=1183102894

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1183102894 file name=+DATA/UGARYT/DATAFILE/sysaux.3815.1183102831
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1183102894 file name=+DATA/UGARYT/DATAFILE/undotbs1.3823.1183102831

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

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 23-OCT-24

RMAN>

3 comments:

Anuj Singh said...

Creating a Standby using RMAN Duplicate (RAC or Non-RAC) (Doc ID 1617946.1)

Anuj Singh said...
This comment has been removed by the author.
Anuj Singh said...





How to perform Rman duplicate on ASM in Rac/Single instance on different host.

Refer the following Article.
Note.382669.1 Duplicate database from non ASM to ASM (vise versa) to a different host
Note.1913937.1 RMAN Duplicate Database From RAC ASM To RAC ASM

How to create a physical Standby using Rman Duplicate
Note.787793.1 Title: Creating a physical standby from ASM primary
Note.374069.1 Step by Step Guide to Create Physical Standby Using RMAN DUPLICATE (non ASM) on different / new host

How to perform Rman duplicate on same node
Note.245262.1 Create a Duplicate Database with NOCATALOG on Same Node

How to perform Rman duplicate using OS backups
Note.419144.1 Creating Duplicate Database from Rman Using the Backups done at OS level

Generic articles on Rman duplicate
Note.452868.1 RMAN 'Duplicate Database' Feature in 11G
Note 228257.1 RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
Note.186127.1 How to Create a Duplicate Database on Windows NT using RMAN
NOTE:1910175.1 - RMAN DUPLICATE / RESTORE including Standby in ASM with OMF / non-OMF / Mixed Name for Datafile / Online Log / Controlfile
NOTE:1913937.1 - STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM
NOTE:415579.1 - HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node

Oracle DBA

anuj blog Archive