Search This Blog

Total Pageviews

Sunday 5 March 2017

Oracle DUPLICATE DATABASE WITHOUT CONNECTION TO TARGET DATABASE

 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




Migrate a Oracle Database From 32Bit To 64 Bit

Oracle Database From  32Bit To 64 Bit migrate

Migrate a Oracle Database From  32Bit To 64 Bit


Metalink Note !!!
Migrate a Database From Linux x86 ( 32-bit ) To Linux Itanium 64-bit (IA64) [ID 553868.1]
Migrating OLAP From 32 To 64 Bits [ID 352306.1]



To Check 32 Bit or 64 Bit !!!! 

[oracle@ora11g2 ~]$ cd $ORACLE_HOME/bin
[oracle@ora11g2 bin]$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped



For OS
SQL> !uname -m
i686


SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum<2 ;

WORD_LENGTH
---------------------------------------------
32-bits


SQL> select metadata from sys.kopm$ ;

METADATA
--------------------------------------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405
0607080823432323081123081141B0230083036907D0030000000000000000000000000000000000
0000000000000000000000000000000000000000

SQL> select metadata from sys.kopm$ ;

If the output references the string 'B023' then it indicates that the database was created as 32-bit
If the output references the string 'B047' then it indicates that the database was created as 64-bit


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


create pfile from 32bit database 

SQL> create pfile from spfile ;

File created.


[oracle@ora11g2 dbs]$ cat initorcl.ora
orcl.__db_cache_size=251658240
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=318767104
orcl.__sga_target=478150656
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=209715200
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/Archive'
*.memory_target=793772032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'



Take rman backup for 32 bit  !! 


RMAN> RUN
 {
 configure controlfile autobackup on;
2> 3> 4> set command id to 'ORCLEEBackupFull';
5> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
6> 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';
7> 8> backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
9> backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
10> 11> }

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

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

Starting backup at 19-FEB-17
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/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c1: starting piece 1 at 19-FEB-17

channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:09:40
Finished backup at 19-FEB-17

Starting Control File and SPFILE Autobackup at 19-FEB-17
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_02_19/o1_mf_s_936356375_dblynrwv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-17

sql statement: alter system archive log current

Starting backup at 19-FEB-17
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=6 RECID=2 STAMP=936355565
input archived log thread=1 sequence=7 RECID=1 STAMP=936355549
input archived log thread=1 sequence=8 RECID=3 STAMP=936356381
input archived log thread=1 sequence=9 RECID=4 STAMP=936356381
channel c1: starting piece 1 at 19-FEB-17
channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/Archive/1_6_936352013.dbf RECID=2 STAMP=936355565
archived log file name=/u01/app/oracle/Archive/1_7_936352013.dbf RECID=1 STAMP=936355549
archived log file name=/u01/app/oracle/Archive/1_8_936352013.dbf RECID=3 STAMP=936356381
archived log file name=/u01/app/oracle/Archive/1_9_936352013.dbf RECID=4 STAMP=936356381
Finished backup at 19-FEB-17

Starting backup at 19-FEB-17
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-FEB-17
channel c1: finished piece 1 at 19-FEB-17
piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL tag=ORCLEE_CON
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17



scp all the backup file to 64 Bit 

create spfile and Modify oratab on 64 bit system 

start the database from 64 bit Binary 



RMAN> startup force nomount ;

Oracle instance started

Total System Global Area     793350144 bytes

Fixed Size                     2217544 bytes
Variable Size                536873400 bytes
Database Buffers             251658240 bytes
Redo Buffers                   2600960 bytes

RMAN> restore controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL' ;

Starting restore at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-17

RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/u01/app/oracle/RmanBackup/';

Starting implicit crosscheck backup at 19-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 19-FEB-17

Starting implicit crosscheck copy at 19-FEB-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-FEB-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_02_19/o1_mf_n_936370038_dbmd011j_.bkp

searching for all files that match the pattern /u01/app/oracle/RmanBackup/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/RmanBackup/ORCL_20170219_4_1_CONTROL

RMAN> restore database ;

Starting restore at 19-FEB-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_1_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 19-FEB-17

RMAN> RESTORE ARCHIVELOG ALL;

Starting restore at 19-FEB-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170219_3_1_ARCHIVE tag=ORCLEE_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 19-FEB-17



******************
For SCN NO 


gen_rman_restore_recovery_script.sql
-- Begin gen_rman_restore_recovery_script.sql
accept oldname prompt "Enter source db name: "
accept newname prompt "Enter target db name: "

set pages 0 head off feed off veri off lines 500 pages 2000 echo off termout off trimspool on

column cmd format a140
spool restore_recover_&&newname\.rman

select 'RUN '||CHR(10)||'{' cmd from dual
union all
--Rename the datafiles
select 'SET NEWNAME FOR DATAFILE '||file#||' TO '''||NNAME||''';' cmd
from (SELECT FILE# ,NAME,replace(replace(name,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname')) NNAME FROM V$DATAFILE) df
union all
--Rename the online redo logs
select 'SQL "ALTER DATABASE RENAME FILE '''''||MEMBER||''''' TO '||chr(10)||' '''''||replace(replace(member,upper('&&oldname'),upper('&&newname')),lower('&&oldname'),lower('&&newname'))||''''' ";' cmd
from (SELECT GROUP#,MEMBER FROM V$LOGFILE)
union all
--Do a SET UNTIL to prevent recovery of the online logs
select 'SET UNTIL SCN '||MINSCN||';' cmd
from (SELECT MIN(SCN) MINSCN FROM (SELECT MAX(NEXT_CHANGE#) SCN FROM V$ARCHIVED_LOG GROUP BY THREAD#))
union all
--restore the database and switch the datafile names
select  'RESTORE DATABASE CHECK READONLY;' cmd from dual
union all
select 'SWITCH DATAFILE ALL;' cmd from dual
union all
--recover the database
select 'RECOVER DATABASE DELETE ARCHIVELOG;' from dual
union all
select '}'||CHR(10)||'EXIT' from dual
/
spool off
exit
-- End gen_rman_restore_recovery_script.sql
******************

RMAN> run {
set until scn 838420;
recover database;
}
2> 3> 4>
executing command: SET until clause

Starting recover at 19-FEB-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/Archive/1_8_936352013.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/Archive/1_9_936352013.dbf
archived log file name=/u01/app/oracle/Archive/1_8_936352013.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/Archive/1_9_936352013.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-17




SQL> select * from dual;

ADDR                   INDX    INST_ID D
---------------- ---------- ---------- -
0000000008ADEC58          0          1 X


SQL>  alter database open resetlogs migrate;

Database altered.



SQL> select * from dual;

D
-
X

run Following Scripts ... 


@?/rdbms/admin/utlirp.sql

@?/rdbms/admin/utlrp.sql

SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================

then shutdown the database ... 

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> !hostname
ora.singh.com

SQL> def
DEFINE _DATE           = "19-FEB-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

SQL> startup ;
ORACLE instance started.

Total System Global Area  793350144 bytes
Fixed Size                  2217544 bytes
Variable Size             536873400 bytes
Database Buffers          251658240 bytes
Redo Buffers                2600960 bytes
Database mounted.
Database opened.


SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum<2 ;

WORD_LENGTH
---------------------------------------------
64-bits


SQL> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(8)<<<<<< for 64 Bit Should be 8
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Oracle DBA

anuj blog Archive