Tuesday, April 9, 2013
Disaster Recovery: Restore Database from One Server to another Server when Different Location
Disaster Recovery: Restore Database from One Server to another Server when Different Location
Step 1: Source DB Node 1
I just created user and allocate default one new tablespace
SQL> create tablespace rmantest datafile 'd:\backup\rman01.dbf' size 100m;
Tablespace created.
SQL> create user rmantest identified by rmantest default tablespace rmantest;
User created.
SQL> grant connect,resource to rmantest;
Grant succeeded.
Step 2: Backup Current Database with archivelog
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> run{
2> allocate channel d1 type disk;
3> backup format 'D:\restore\backup\data_t%t_s5s_p%p' database plus archivelog;
4> release channel d1;
5> }
allocated channel: d1
channel d1: SID=24 device type=DISK
Starting backup at 13-MAR-11
3 Mohamed Azar | http://mohamedazar.wordpress.com
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=745666576
input archived log thread=1 sequence=3 RECID=2 STAMP=745669135
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669135_S5S_P1 tag=TAG20110313T101855 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 13-MAR-11
Starting backup at 13-MAR-11
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\MAZAR\ORADATA\RMANTEST\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\MAZAR\ORADATA\RMANTEST\SYSAUX01.DBF
input datafile file number=00005 name=D:\BACKUP\RMAN01.DBF
input datafile file number=00003 name=C:\APP\MAZAR\ORADATA\RMANTEST\UNDOTBS01.DBF
input datafile file number=00004 name=C:\APP\MAZAR\ORADATA\RMANTEST\USERS01.DBF
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:05
Finished backup at 13-MAR-11
Starting backup at 13-MAR-11
4 Mohamed Azar | http://mohamedazar.wordpress.com
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=3 STAMP=745669209
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669210_S5S_P1 tag=TAG20110313T102009 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-11
Starting Control File and SPFILE Autobackup at 13-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\RMANTEST\AUTOBACKUP\2011_03_13\O1_MF_S_745669211_6QRW1DF0_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13-MAR-11
released channel: d1
Step 3: Again I just created one table and inserted one row for check Consistent data available after a restored database to another node.
SQL> conn rmantest/rmantest
Connected.
SQL> create table a(city varchar2(20));
Table created.
SQL> insert into a values('riyadh');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
5 Mohamed Azar | http://mohamedazar.wordpress.com
SQL> alter system switch logfile;
System altered.
Step 4:
* Unfortunately I don’t have another node, So I just deleted my database and I try to restore database into another location.
* If you are going to restore database to another node, you need to copy bakup files and all archivelog files into one location.
Copy backup files
Copy All Archivelog files
Copy Control files & SPfiles backup .
The location like below
D:\restore\backup>dir
Volume in drive D has no label.
Volume Serial Number is 3861-730C
Directory of D:\restore\backup
13-Mar-11 12:53 PM <DIR> .
13-Mar-11 12:53 PM <DIR> ..
13-Mar-11 12:52 PM <DIR> back
13-Mar-11 10:18 AM 64,855,040 DATA_T745669135_S5S_P1
13-Mar-11 10:20 AM 1,022,410,752 DATA_T745669143_S5S_P1
13-Mar-11 10:20 AM 23,552 DATA_T745669210_S5S_P1
13-Mar-11 09:36 AM 44,904,960 O1_MF_1_2_6QRSGZ0J_.ARC
13-Mar-11 10:18 AM 19,949,056 O1_MF_1_3_6QRVYYKX_.ARC
13-Mar-11 10:20 AM 22,016 O1_MF_1_4_6QRW19PB_.ARC
13-Mar-11 10:28 AM 262,144 O1_MF_1_5_6QRWJTQ9_.ARC
13-Mar-11 10:20 AM 9,830,400 O1_MF_S_745669211_6QRW1DF0_.BKP
8 File(s) 1,162,257,920 bytes
3 Dir(s) 90,152,288,256 bytes free
D:\restore\backup>
6 Mohamed Azar | http://mohamedazar.wordpress.com
Step 5: Create New Instance and Create Oracle Password file
C:\Windows\system32>oradim -new -sid rmantest
Instance created.
C:\Windows\system32>orapwd file=C:\app\mazar\product\11.2.0\dbhome_1\database\PWDrmantest.ora password=rman123
Step 6: Connect rman and startup nomount without initialization file.
C:\Windows\system32>rman target sys/rman123
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 13 10:35:07 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\APP\MAZAR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITRMANTEST.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1373264 bytes
Variable Size 75500464 bytes
Database Buffers 75497472 bytes
Redo Buffers 6647808 bytes
Step 7: Restore spfile from backup
RMAN> restore spfile from 'D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP';
Starting restore at 13-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
7 Mohamed Azar | http://mohamedazar.wordpress.com
channel ORA_DISK_1: SID=96 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-MAR-11.
Step 8: Create pfile from spfile
Enter user-name: sys/rman123 as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='d:\restore\initrman.ora' from spfile;
File created.
Step 9 : Shutdown db
RMAN> shutdown immediate
Oracle instance shut down
Step 10 : Edit Pfile
Now You need to edit Initialization file, Your new controlfile location, dumpfile location. Here I just Edit Controlfile location only because I need to restore different location.
*.control_files='D:\restore\control01.ctl','D:\restore\control02.ctl'
Step 11: Startup db using pfile
Enter user-name: sys/rman123 as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='D:\restore\initrman.ora';
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
8 Mohamed Azar | http://mohamedazar.wordpress.com
Variable Size 201327056 bytes
Database Buffers 327155712 bytes
Redo Buffers 5804032 bytes
SQL> create spfile from pfile='D:\restore\initrman.ora';
File created.
Step 12 : Again I shutdown db and restarted using spfile
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
RMAN> startup nomount
Oracle instance started
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 201327056 bytes
Database Buffers 327155712 bytes
Redo Buffers 5804032 bytes
Step 13: Restore Controlfile
RMAN> restore controlfile from 'D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP';
Starting restore at 13-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\RESTORE\CONTROL01.CTL
output file name=D:\RESTORE\CONTROL02.CTL
Finished restore at 13-MAR-11.
9 Mohamed Azar | http://mohamedazar.wordpress.com
Step 14: Mount the database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1.
Step 15: Rename Logfile to new Location.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO03.LOG' to 'D:\restore\REDO03.LOG';
Database altered.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO02.LOG' to 'D:\restore\REDO02.LOG';
Database altered.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO01.LOG' to 'D:\restore\REDO01.LOG';
Database altered.
Step 16 : catalog backuppiece
RMAN> catalog start with 'd:\restore\backup';
searching for all files that match the pattern d:\restore\backup
List of Files Unknown to the Database
=====================================
File Name: D:\RESTORE\backup\DATA_T745669135_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669143_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669210_S5S_P1
File Name: D:\RESTORE\backup\O1_MF_1_2_6QRSGZ0J_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_3_6QRVYYKX_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_4_6QRW19PB_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_5_6QRWJTQ9_.ARC
File Name: D:\RESTORE\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
Do you really want to catalog the above files (enter YES or NO)? y
10 Mohamed Azar | http://mohamedazar.wordpress.com
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\RESTORE\backup\DATA_T745669135_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669143_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669210_S5S_P1
File Name: D:\RESTORE\backup\O1_MF_1_2_6QRSGZ0J_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_3_6QRVYYKX_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_4_6QRW19PB_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_5_6QRWJTQ9_.ARC
File Name: D:\RESTORE\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
Step 17: Restore DB & Recover DB
RMAN> run{
2> set newname for datafile 1 to 'd:\restore\system01.dbf';
3> set newname for datafile 2 to 'd:\restore\sysaux01.dbf';
4> set newname for datafile 3 to 'd:\restore\undotbs01.dbf';
5> set newname for datafile 4 to 'd:\restore\users01.dbf';
6> set newname for datafile 5 to 'd:\restore\rman01.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-MAR-11
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
11 Mohamed Azar | http://mohamedazar.wordpress.com
channel ORA_DISK_1: restoring datafile 00001 to d:\restore\system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to d:\restore\sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to d:\restore\undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to d:\restore\users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to d:\restore\rman01.dbf
channel ORA_DISK_1: reading from backup piece D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1
channel ORA_DISK_1: piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 13-MAR-11
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=745678470 file name=D:\RESTORE\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=745678470 file name=D:\RESTORE\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=745678470 file name=D:\RESTORE\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=745678470 file name=D:\RESTORE\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=745678470 file name=D:\RESTORE\RMAN01.DBF
Starting recover at 13-MAR-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC
archived log for thread 1 with sequence 5 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC thread=1 sequence=4
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/13/2011 12:54:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 967587
12 Mohamed Azar | http://mohamedazar.wordpress.com
So you Need to fix this solution like below,
RMAN> run{
2> set until sequence 6 thread 1;
3> set newname for datafile 1 to 'd:\restore\system01.dbf';
4> set newname for datafile 2 to 'd:\restore\sysaux01.dbf';
5> set newname for datafile 3 to 'd:\restore\undotbs01.dbf';
6> set newname for datafile 4 to 'd:\restore\users01.dbf';
7> set newname for datafile 5 to 'd:\restore\rman01.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }
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
Starting restore at 13-MAR-11
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 d:\restore\system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to d:\restore\sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to d:\restore\undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to d:\restore\users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to d:\restore\rman01.dbf
channel ORA_DISK_1: reading from backup piece D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1
channel ORA_DISK_1: piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 13-MAR-11
13 Mohamed Azar | http://mohamedazar.wordpress.com
Starting recover at 13-MAR-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC
archived log for thread 1 with sequence 5 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC thread=1 sequence=4
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-MAR-11
RMAN> alter database open resetlogs;
database opened
RMAN>
Step 18: Now My Database was restored successfully, So I need to check whether my data available or not.
C:\Users\mazar>set oracle_sid=rmantest
C:\Users\mazar>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 13 12:58:53 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys/rman123 as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn rmantest/rmantest;
Connected.
SQL> select * from tab;
14 Mohamed Azar | http://mohamedazar.wordpress.com
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
SQL> select * from a;
CITY
--------------------
riyadh
SQL>
Oracle 11gR2 : Recover dropped tablespace using RMAN tablespace point in time recovery
Oracle 11gR2 : Recover dropped tablespace using RMAN tablespace point in time recovery
This is new feature for Oracle 11g R2.Here I'm go to demonstrate How to recover dropped Tablespace using RMAN Tablespace point in time recovery.
Step 1: Create Tablespace
SQL> conn / as sysdba
Connected.
SQL> create tablespace testtbs datafile 'd:\backup\testtbs01.dbf' size 100m;
Tablespace created.
SQL> create user testtbs identified by testtbs default tablespace testtbs;
User created.
SQL> grant connect,resource to testtbs;
Grant succeeded.
SQL> conn testtbs/testtbs;
Connected.
SQL> create table test(empname varchar2(20),city varchar2(20));
Table created.
SQL> insert into test values('azar','riyadh');
1 row created.
SQL> insert into test values('jabar','chennai');
1 row created.
SQL> commit;
Commit complete.
SQL> conn testdb/testdb;
Connected.
(Note : This table allocated for other tablespace , i just insert data for this table for check data consistent after doing TBPITR)
SQL> create table d(empname varchar2(20));
Table created.
SQL> insert into d values('kareem');
1 row created.
SQL> insert into d values('syed');
3 Mohamed Azar || http://mohamedazar.wordpress.com
1 row created.
SQL> commit;
Commit complete.
Step 2: Backup database Plus archivelog
C:\Users\mazar>set oracle_sid=azardb
C:\Users\mazar>rman target sys/Admin123
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 15:39:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: AZARDB (DBID=1652383192)
RMAN> backup database plus archivelog;
Starting backup at 01-JAN-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
Step 3: Note Current SCN
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8448197
Step 4: Drop tablespace
SQL> drop tablespace testtbs including contents and datafiles;
Tablespace dropped.
Step 5: I just added data for another table allocated for other tablespace due to just check for data consistent.
SQL> conn testdb/testdb;
Connected.
SQL> insert into d values('azmi');
1 row created.
4 Mohamed Azar || http://mohamedazar.wordpress.com
SQL> commit;
Commit complete.
Step 6: Create auxiliary & Recover tablespace using tablespace point in time recovery.
C:\Windows\system32>rman target sys/Admin123
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 1 14:45:08 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: AZARDB (DBID=1652383192)
RMAN> run{
2> recover tablespace testtbs
3> until scn 8448197
4> auxiliary destination 'D:\backupnew';
5> }
executing command: SET NEWNAME
Starting recover at 01-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2
Creating automatic instance, with SID='Bopd'
initialization parameters used for automatic instance:
db_name=AZARDB
db_unique_name=Bopd_tspitr_AZARDB
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=D:\backupnew
log_archive_dest_1='location=D:\backupnew'
#No auxiliary parameter file used
starting up automatic instance AZARDB
Oracle instance started
Total System Global Area 292933632 bytes
Fixed Size 1374164 bytes
5 Mohamed Azar || http://mohamedazar.wordpress.com
Variable Size 100665388 bytes
Database Buffers 184549376 bytes
Redo Buffers 6344704 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace testtbs
contents of Memory Script:
{
# set requested point in time
set until scn 8448197;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 01-JAN-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=59 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 C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP
channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\AUTOBACKUP\2011_01_01\O1_MF_N_739286122_6KXZOWTT_.BKP tag=TAG20110101T13152
1
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:00
output file name=D:\BACKUPNEW\AZARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL
Finished restore at 01-JAN-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until scn 8448197;
# set destinations for recovery set and auxiliary set datafiles
6 Mohamed Azar || http://mohamedazar.wordpress.com
set newname for clone datafile 1 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 3 to
"D:\BACKUP\TESTTBS01.DBF";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 8, 2, 3;
switch clone datafile all;
}
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
renamed tempfile 1 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
Starting restore at 01-JAN-11
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 D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00008 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\BACKUP\TESTTBS01.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_NNNDF_TAG20110101T131020_6KXZDFVB_.
BKP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:06
Finished restore at 01-JAN-11
datafile 1 switched to datafile copy
input datafile copy RECID=388 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF
datafile 8 switched to datafile copy
7 Mohamed Azar || http://mohamedazar.wordpress.com
input datafile copy RECID=389 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=390 STAMP=739291881 file name=D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF
contents of Memory Script:
{
# set requested point in time
set until scn 8448197;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 8 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 3 online";
# recover and open resetlogs
recover clone database tablespace "TESTTBS", "SYSTEM", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
Starting recover at 01-JAN-11
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_
.ARC
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=16
channel ORA_AUX_DISK_1: reading from backup piece C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.
BKP
channel ORA_AUX_DISK_1: piece handle=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\BACKUPSET\2011_01_01\O1_MF_ANNNN_TAG20110101T131514_6KXZOPGN_.BKP tag=TAG20
110101T131514
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
8 Mohamed Azar || http://mohamedazar.wordpress.com
archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=D:\BACKUPNEW\ARC0000000016_0738861267.0001 RECID=777 STAMP=739291886
archived log file name=C:\APP\ORACLE\MAZAR\FLASH_RECOVERY_AREA\AZARDB\ARCHIVELOG\2011_01_01\O1_MF_1_17_6KY53B3J_.ARC thread=1 sequence=17
media recovery complete, elapsed time: 00:00:06
Finished recover at 01-JAN-11
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TESTTBS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
D:\backupnew''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
D:\backupnew''";
}
executing Memory Script
sql statement: alter tablespace TESTTBS read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backupnew''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_Bopd":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_Bopd" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Bopd is:
EXPDP> D:\BACKUPNEW\TSPITR_BOPD_37645.DMP
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TESTTBS:
EXPDP> D:\BACKUP\TESTTBS01.DBF
EXPDP> Job "SYS"."TSPITR_EXP_Bopd" successfully completed at 15:12:24
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script
database closed
9 Mohamed Azar || http://mohamedazar.wordpress.com
database dismounted
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_Bopd" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_Bopd":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_Bopd" successfully completed at 15:16:47
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TESTTBS read write';
sql 'alter tablespace TESTTBS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TESTTBS read write
sql statement: alter tablespace TESTTBS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_TEMP_6KY5C761_.TMP deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_4_6KY5BT82_.LOG deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_3_6KY5BQSZ_.LOG deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_2_6KY5BOYS_.LOG deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\ONLINELOG\O1_MF_1_6KY5BMV3_.LOG deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSAUX_6KY53YG4_.DBF deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_UNDOTBS2_6KY53YJT_.DBF deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\DATAFILE\O1_MF_SYSTEM_6KY53Y6C_.DBF deleted
auxiliary instance file D:\BACKUPNEW\AZARDB\CONTROLFILE\O1_MF_6KY50OH2_.CTL deleted
Finished recover at 01-JAN-11
RMAN>
Step 7: Check tablespace status
SQL> select status,tablespace_name from dba_tablespaces where tablespace_name like 'TESTTBS%';;
STATUS TABLESPACE_NAME
--------- ------------------------------
OFFLINE TESTTBS
11 rows selected.
10 Mohamed Azar || http://mohamedazar.wordpress.com
Alter tablespace online
SQL> alter tablespace testtbs online;
Tablespace altered.
Step 8: Check table
SQL> conn testtbs/testtbs;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> select * from test;
EMPNAME CITY
-------------------- --------------------
azar riyadh
jabar chennai
SQL>
And also I'm go to check other table for data consistent.
SQL> conn testdb/testdb;
Connected.
SQL> select * from d;
EMPNAME
--------------------
azmi
kareem
syed
Now successfully recovered dropped tablespace using RMAN point in time recovery.
Duplicate Database from ASM to Non- ASM Database Using RMAN
Duplicate Database from ASM to Non- ASM Database Using RMAN
Step 1:
Determine how much disk space will be required.
SQL> select name from v$database;
NAME
---------
AZARDB
Calculate total space for all datafiles within database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1310 150
18.59375 1478.59375
Calculate space for list of datafiles within primary database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1165 150
18.59375 1333.59375
3 Mohamed Azar | http://mohamedazar.wordpress.com
Step 2: backup Source database
RMAN> run{
2> allocate channel d1 type disk;
3> backup format '/backups/df_t%t_s%s_p%p' database;
4> sql 'alter system archive log current';
5> backup format '/backups/al_t%t_s%s_p%p' archivelog all;
6> release channel d1;
7> }
released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=44 device type=DISK
Starting backup at 17-DEC-10
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/azardb/datafile/system.256.736179685
input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685
input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971
input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685
input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256.737964169
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:02:27
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-10
sql statement: alter system archive log current
Starting backup at 17-DEC-10
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=736268182
input archived log thread=1 sequence=4 RECID=2 STAMP=736345263
input archived log thread=1 sequence=5 RECID=3 STAMP=736438711
input archived log thread=1 sequence=6 RECID=4 STAMP=737551583
input archived log thread=1 sequence=7 RECID=5 STAMP=737880523
4 Mohamed Azar | http://mohamedazar.wordpress.com
input archived log thread=1 sequence=8 RECID=6 STAMP=737897929
input archived log thread=1 sequence=9 RECID=7 STAMP=737962069
input archived log thread=1 sequence=10 RECID=8 STAMP=737969217
input archived log thread=1 sequence=11 RECID=9 STAMP=737969220
input archived log thread=1 sequence=12 RECID=10 STAMP=737969948
input archived log thread=1 sequence=13 RECID=11 STAMP=737969948
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/al_t737969948_s8_p1 tag=TAG20101217T073908 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-DEC-10
released channel: d1
RMAN>
Step 3: Create pfile & edit some required parameters
SQL> create pfile='/backups/initAUX.ora' from spfile;
File created.
SQL>
Edit& Save
*.audit_file_dest =/u01/app/oracle/AUX/adump
*.core_dump_dest =/u01/app/oracle/AUX/cdump
*.db_name ="AUX"
*.instance_name =AUX
*.control_files =/u01/app/oracle/AUX/control01.ctl
*.db_file_name_convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX")
*.log_file_name_convert =("+DATA","/u01/app/oracle/AUX")
*.undo_management =AUTO
*.undo_retention =10800
*.undo_tablespace='UNDOTBS1'
*.db_block_size=8192
*.compatible='11.2.0.0.0'
Step 4: startup nomount for AUX database
[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
5 Mohamed Azar | http://mohamedazar.wordpress.com
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount pfile='/backups/initAUX.ora';
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL>
Step 5:Create duplicate database from here
[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)
RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=20 device type=DISK
Starting Duplicate Db at 17-DEC-10
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
6 Mohamed Azar | http://mohamedazar.wordpress.com
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''AZARDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' 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 = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 146472960 bytes
7 Mohamed Azar | http://mohamedazar.wordpress.com
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
Starting restore at 17-DEC-10
channel C1: starting datafile backup set restore
channel C1: restoring control file
channel C1: reading from backup piece /backups/df_t737969943_s7_p1
channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/AUX/control01.ctl
Finished restore at 17-DEC-10
database mounted
contents of Memory Script:
{
set until scn 971599;
set newname for datafile 1 to
"/u01/app/oracle/AUX/azardb/datafile/system.256.736179685";
set newname for datafile 2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile 3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile 4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile 5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
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
8 Mohamed Azar | http://mohamedazar.wordpress.com
executing command: SET NEWNAME
Starting restore at 17-DEC-10
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
channel C1: reading from backup piece /backups/df_t737969795_s6_p1
channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:01:27
Finished restore at 17-DEC-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
set until scn 971599;
9 Mohamed Azar | http://mohamedazar.wordpress.com
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-DEC-10
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
10 Mohamed Azar | http://mohamedazar.wordpress.com
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: alter system set db_name = ''AUX'' 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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
11 Mohamed Azar | http://mohamedazar.wordpress.com
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Duplicate database was successfully created
Step 6: Check Duplicate Database
[oracle@localhost ~]$ export ORACLE_SID=AUX
12 Mohamed Azar | http://mohamedazar.wordpress.com
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:25:45 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
AUX
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
SQL>
Possible Errors:
You can get some errors , cann't restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.
azardb/datafile
azardb/onlinelog
Step 1:
Determine how much disk space will be required.
SQL> select name from v$database;
NAME
---------
AZARDB
Calculate total space for all datafiles within database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1310 150
18.59375 1478.59375
Calculate space for list of datafiles within primary database
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;
DataFile Size Mb Redo Log Size Mb
---------------- ----------------
Control File Size Mb Total Size Mb
-------------------- -------------
1165 150
18.59375 1333.59375
3 Mohamed Azar | http://mohamedazar.wordpress.com
Step 2: backup Source database
RMAN> run{
2> allocate channel d1 type disk;
3> backup format '/backups/df_t%t_s%s_p%p' database;
4> sql 'alter system archive log current';
5> backup format '/backups/al_t%t_s%s_p%p' archivelog all;
6> release channel d1;
7> }
released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=44 device type=DISK
Starting backup at 17-DEC-10
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/azardb/datafile/system.256.736179685
input datafile file number=00002 name=+DATA/azardb/datafile/sysaux.257.736179685
input datafile file number=00005 name=+DATA/azardb/datafile/example.269.736179971
input datafile file number=00003 name=+DATA/azardb/datafile/undotbs1.258.736179685
input datafile file number=00004 name=+SPTEST/azardb/datafile/users.256.737964169
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:02:27
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-10
sql statement: alter system archive log current
Starting backup at 17-DEC-10
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=736268182
input archived log thread=1 sequence=4 RECID=2 STAMP=736345263
input archived log thread=1 sequence=5 RECID=3 STAMP=736438711
input archived log thread=1 sequence=6 RECID=4 STAMP=737551583
input archived log thread=1 sequence=7 RECID=5 STAMP=737880523
4 Mohamed Azar | http://mohamedazar.wordpress.com
input archived log thread=1 sequence=8 RECID=6 STAMP=737897929
input archived log thread=1 sequence=9 RECID=7 STAMP=737962069
input archived log thread=1 sequence=10 RECID=8 STAMP=737969217
input archived log thread=1 sequence=11 RECID=9 STAMP=737969220
input archived log thread=1 sequence=12 RECID=10 STAMP=737969948
input archived log thread=1 sequence=13 RECID=11 STAMP=737969948
channel d1: starting piece 1 at 17-DEC-10
channel d1: finished piece 1 at 17-DEC-10
piece handle=/backups/al_t737969948_s8_p1 tag=TAG20101217T073908 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-DEC-10
released channel: d1
RMAN>
Step 3: Create pfile & edit some required parameters
SQL> create pfile='/backups/initAUX.ora' from spfile;
File created.
SQL>
Edit& Save
*.audit_file_dest =/u01/app/oracle/AUX/adump
*.core_dump_dest =/u01/app/oracle/AUX/cdump
*.db_name ="AUX"
*.instance_name =AUX
*.control_files =/u01/app/oracle/AUX/control01.ctl
*.db_file_name_convert =("+DATA","/u01/app/oracle/AUX","+SPTEST","/u01/app/oracle/AUX")
*.log_file_name_convert =("+DATA","/u01/app/oracle/AUX")
*.undo_management =AUTO
*.undo_retention =10800
*.undo_tablespace='UNDOTBS1'
*.db_block_size=8192
*.compatible='11.2.0.0.0'
Step 4: startup nomount for AUX database
[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:14:09 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
5 Mohamed Azar | http://mohamedazar.wordpress.com
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount pfile='/backups/initAUX.ora';
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL>
Step 5:Create duplicate database from here
[oracle@localhost ~]$ export ORACLE_SID=AUX
[oracle@localhost ~]$ rman target sys/Admin123@azardb auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 17 09:18:34 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: AZARDB (DBID=1655311948)
connected to auxiliary database: AUX (not mounted)
RMAN> run{
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database to AUX;
4> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=20 device type=DISK
Starting Duplicate Db at 17-DEC-10
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
6 Mohamed Azar | http://mohamedazar.wordpress.com
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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''AZARDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' 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 = ''AZARDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 146472960 bytes
7 Mohamed Azar | http://mohamedazar.wordpress.com
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
Starting restore at 17-DEC-10
channel C1: starting datafile backup set restore
channel C1: restoring control file
channel C1: reading from backup piece /backups/df_t737969943_s7_p1
channel C1: piece handle=/backups/df_t737969943_s7_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/AUX/control01.ctl
Finished restore at 17-DEC-10
database mounted
contents of Memory Script:
{
set until scn 971599;
set newname for datafile 1 to
"/u01/app/oracle/AUX/azardb/datafile/system.256.736179685";
set newname for datafile 2 to
"/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685";
set newname for datafile 3 to
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685";
set newname for datafile 4 to
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169";
set newname for datafile 5 to
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
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
8 Mohamed Azar | http://mohamedazar.wordpress.com
executing command: SET NEWNAME
Starting restore at 17-DEC-10
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to /u01/app/oracle/AUX/azardb/datafile/system.256.736179685
channel C1: restoring datafile 00002 to /u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
channel C1: restoring datafile 00003 to /u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
channel C1: restoring datafile 00004 to /u01/app/oracle/AUX/azardb/datafile/users.256.737964169
channel C1: restoring datafile 00005 to /u01/app/oracle/AUX/azardb/datafile/example.269.736179971
channel C1: reading from backup piece /backups/df_t737969795_s6_p1
channel C1: piece handle=/backups/df_t737969795_s6_p1 tag=TAG20101217T073635
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:01:27
Finished restore at 17-DEC-10
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=737976075 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
set until scn 971599;
9 Mohamed Azar | http://mohamedazar.wordpress.com
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-DEC-10
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947
archived log for thread 1 with sequence 13 is already on disk as file +DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_12.279.737969947 thread=1 sequence=12
archived log file name=+DATA/azardb/archivelog/2010_12_17/thread_1_seq_13.280.737969949 thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
10 Mohamed Azar | http://mohamedazar.wordpress.com
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: alter system set db_name = ''AUX'' 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 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
allocated channel: C1
channel C1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_1.262.736179919', '/u01/app/oracle/AUX/azardb/onlinelog/group_1.263.736179919' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_2.264.736179921', '/u01/app/oracle/AUX/azardb/onlinelog/group_2.265.736179925' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/AUX/azardb/onlinelog/group_3.266.736179927', '/u01/app/oracle/AUX/azardb/onlinelog/group_3.267.736179929' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/AUX/azardb/datafile/system.256.736179685'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685",
"/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685",
"/u01/app/oracle/AUX/azardb/datafile/users.256.737964169",
"/u01/app/oracle/AUX/azardb/datafile/example.269.736179971";
11 Mohamed Azar | http://mohamedazar.wordpress.com
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/AUX/azardb/tempfile/temp.268.736179955 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685 RECID=1 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685 RECID=2 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169 RECID=3 STAMP=737976112
cataloged datafile copy
datafile copy file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971 RECID=4 STAMP=737976113
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=737976112 file name=/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=737976113 file name=/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Duplicate database was successfully created
Step 6: Check Duplicate Database
[oracle@localhost ~]$ export ORACLE_SID=AUX
12 Mohamed Azar | http://mohamedazar.wordpress.com
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 09:25:45 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
AUX
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/AUX/azardb/datafile/system.256.736179685
/u01/app/oracle/AUX/azardb/datafile/sysaux.257.736179685
/u01/app/oracle/AUX/azardb/datafile/undotbs1.258.736179685
/u01/app/oracle/AUX/azardb/datafile/users.256.737964169
/u01/app/oracle/AUX/azardb/datafile/example.269.736179971
SQL>
Possible Errors:
You can get some errors , cann't restore datafile & onlineredo log files, in this case you need to create directory ,It should be indicate.
azardb/datafile
azardb/onlinelog
Subscribe to:
Posts (Atom)