Oracle Duplicate database from backup
DUPLICATE DATABASE WITHOUT CONNECTION TO TARGET DATABASE
Aim to create duplicate database from orcl to orcld from oracle backup
Rman script !!!!!
RUN
{ configure controlfile autobackup on;
set command id to 'ORCLBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEE_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup spfile format '/u01/app/oracle/RmanBackup/spfile_%d_%s_%T_dbid%I';
backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}
Backup is in following dir
[oracle@ora ~]$ cd /u01/app/oracle/RmanBackup/
[oracle@ora RmanBackup]$ ls -ltr
total 283432
-rw-r----- 1 oracle oinstall 280109056 Feb 26 20:23 ORCL_20170226_1_1_FULL
-rw-r----- 1 oracle oinstall 227328 Feb 26 20:23 ORCL_20170226_3_1_ARCHIVE
-rw-r----- 1 oracle oinstall 98304 Feb 26 20:23 spfile_ORCL_4_20170226_dbid1464774582
-rw-r----- 1 oracle oinstall 9797632 Feb 26 20:24 ORCL_20170226_5_1_CONTROL
cat /etc/oratab
# Create this in oratab file
orcld:/u01/app/oracle/product/11.2.4/db_1:N
Create all the Dir ..
[oracle@ora dbs]$ cat initorcld.ora
orcl.__db_cache_size=377487360
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=184549376
orcl.__sga_target=536870912
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcld/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcld/control01.ctl','/u01/app/oracle/oradata/orcld/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcld'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/Archived'
*.open_cursors=300
*.pga_aggregate_target=182452224
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=('orcl','orcld')
*.log_file_name_convert=('orcl','orcld')
edit oratab file
orcld:/u01/app/oracle/product/11.2.4/db_1:N
[oracle@ora dbs]$ . oraenv
ORACLE_SID = [vihaan] ? orcld
The Oracle base has been set to /u01/app/oracle
SQL> create spfile from pfile;
File created.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 213911448 bytes
Database Buffers 314572800 bytes
Redo Buffers 3723264 bytes
[oracle@ora RmanBackup]$ rman nocatalog | tee /home/oracle/Rmanlog.log
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 26 20:45:28 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliary /
connected to auxiliary database: ORCLD (not mounted)
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
RMAN> duplicate database to orcld backup location '/u01/app/oracle/RmanBackup/' ;
RMAN>
Starting Duplicate Db at 26-FEB-17
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCLD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/app/oracle/RmanBackup/ORCL_2017
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN
sql statement: alter system set db_unique_name = ''ORCLD'' comment= ''Modified
Oracle instance shut down
Oracle instance started
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 213911448 bytes
Database Buffers 314572800 bytes
Redo Buffers 3723264 bytes
Starting restore at 26-FEB-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/orcld/control01.ctl
output file name=/u01/app/oracle/oradata/orcld/control02.ctl
Finished restore at 26-FEB-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 932428;
set newname for datafile 1 to
"/u01/app/oracle/oradata/orcld/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/orcld/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/orcld/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/orcld/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 26-FEB-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/orcld/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcld/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcld/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcld/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170226_1_1_FULL
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170226_1_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 26-FEB-17
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=936996422 file name=/u01/app/oracle/oradata/orcld/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=936996422 file name=/u01/app/oracle/oradata/orcld/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=936996422 file name=/u01/app/oracle/oradata/orcld/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=936996422 file name=/u01/app/oracle/oradata/orcld/users01.dbf
contents of Memory Script:
{
set until scn 932428;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 26-FEB-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=7
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170226_3_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170226_3_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/Archived/1_7_936993846.dbf thread=1 sequence=7
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/Archived/1_7_936993846.dbf RECID=1 STAMP=936996424
archived log file name=/u01/app/oracle/Archived/1_8_936993846.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/Archived/1_8_936993846.dbf RECID=2 STAMP=936996424
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-FEB-17
Oracle instance started
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 213911448 bytes
Database Buffers 314572800 bytes
Redo Buffers 3723264 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCLD'' 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 = ''ORCLD'' 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 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 213911448 bytes
Database Buffers 314572800 bytes
Redo Buffers 3723264 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCLD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/orcld/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/orcld/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/orcld/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/orcld/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/orcld/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/orcld/sysaux01.dbf",
"/u01/app/oracle/oradata/orcld/undotbs01.dbf",
"/u01/app/oracle/oradata/orcld/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/orcld/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcld/sysaux01.dbf RECID=1 STAMP=936996434
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcld/undotbs01.dbf RECID=2 STAMP=936996434
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orcld/users01.dbf RECID=3 STAMP=936996434
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=936996434 file name=/u01/app/oracle/oradata/orcld/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=936996434 file name=/u01/app/oracle/oradata/orcld/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=936996434 file name=/u01/app/oracle/oradata/orcld/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 26-FEB-17
========================
set pages 100 lines 300
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from DBA_TEMP_FILES dd, V$TEMPFILE df
where df.name=dd.file_name
and tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY')
order by 1,2,6;
TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAXSIZE_MB CREATION_TIME
------------------------------ ---------------------------------------------------------------------- -------- --- ---------- ---------------
TEMP /u01/app/oracle/oradata/orcld/temp01.dbf 20.00 YES ######## 26-FEB-17
No comments:
Post a Comment