One of the most unpleasant DBA tasks is to be first aid in total database disaster situation or in situation when you need to reinstall system that someone abandoned or suddenly left. In any case, client has very limited number of useful information and the whole work is to research from the bottom to top to bring system up and runing.
In this topic I'll try to explain how to restore Oracle database which has been installed on server which is destroyed (or resume someone failed installation). The only thing that must exist ... any kind of backup. In this case I'll speak about RMAN backup, because even the most inexperienced user know that some kind of backup is needed and RMAN is unbeatable combination.
Here are classical problems that DBA will face in a worst case scenario:
- Database version
- Unknown database structure
- Database core structure
- content of init.ora file
- Identify backup location
- Identify control file in backup
- Identify necessary directory structure
In mine case I knew three things:
- Oracle version was 9.2.0.6
- Database name was DB1 (name intentionally changed to protect client data)
- Backup was done with RMAN to "e:\BACKUP\ORACLE\" directory
This seems to be enough to start restore procedure.
However if you are unlucky, grepping binary backup files could say to you many things. One of them is especially interesting-RMAN backup of controlfile, which is comprehensive source of information. This file is 10-100 MB in size, If database was using pfile, then those data may be found as well (picture is taken as example of other database):
On the other hand, in RMAN controfile backup, many database directory structure can be read as well (picture is taken as example of other database):
Because I'l restore database in different box, different directory, these data may be more like "nice to know" information because many of them we'll find out after successful controlfile restore.
However, every disaster/restore scenario might be specified in next easy to understand steps:
- Install software
- Create necessary directory structure
- Create (restore) spfile
- Restore controlfile
- Restore database
- Recover "up to" the archivelog (until time you want to)
- Open database with reset logs option
- BACKUP NEW DATABASE
Initial settings
Initial settings are divided in several easy steps which must succeed without any error or mistake.
Install software
I was not forced to install Windows (2003 R2 was certified platform) so I have to install only correct Oracle 9.2.0.1 and then patch to 9.2.0.6 version. If you do not know the correct patch version RMAN restore will help you with messages, and even more alert.log file could show you some interesting details.
Set important environment
Next step is to set up correct environment. In mine case I was using box with some previously installed Oracle software (Oracle 10.2) so it was crucial to set up correct data:
G:\>SET ORACLE_SID=DB1
G:\>SET ORACLE_HOME=c:\oracle\Ora92
G:\>set Path=C:\oracle\ora92\bin;C:\oracle\ora92\jre\1.4.2\bin\client;C:\oracle\ora92\jre\1.4.2\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
Ensure that all this is placed in one batch file to be sure that no other paths/environment are affecting your current Oracle work. Once again this is crucial to ensure full control over the process especially when you are doing this on the box where other Oracle software previously exists!
Create necessary directory structure
However, it is more then welcome to create all directories. For that we'll need:
- bdump,cdump,udump directories (where database log and trace files are written). In mine case they will be under "G:\admin\DB1" directory with same name sub directories
- oradata directory ("g:\oradata\DB1") where datafiles will be restored. As you see in mine case all datafiles are restored in one dir what gives you more control over restore scripts (especially if you do not know database structure)
- archivelog directory ("G:\oradata\DB1\archive") where archivelog files will be unpacked from backup
- Later you'll see that another directory should be made for redolog images, so I'll created now "G:\oradata\DB1\log"
So when we'll force directory structure for mentioned directories, let us put them in init.ora file as well.
Create initDB1.ora file with only two entries
db_name=DB1
job_queue_processes=0
First one is only required parameter and second one is mine insurance that after successful restore/recover started instance could not start some job which may harm any date (db_link!).
Create Instance service
Oracle instance on Windows is rather different then the same process on Linux (where you can skip this step).
G:\>oradim -new -sid %ORACLE_SID% -intpwd newpwd -startmode M
Instance created.
G:\>
I prefer to check in
services.msc console to see that service has really started. In a case of any problems add current Windows user to
ora_dba group.
Create password file
Position in directory %ORACLE_HOME%\database, and start
orapwd utility to create
PWDDB1.ora password file:
g:\> orapwd file=PWDDB1.ora password= entries=10 force=y
Start instance in nomount mode
After starting service, we have to start instance in nomount mode (RMAN or sqlplus).
G:>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Sri Ruj 15 19:11:45 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='g:\initDB1.ora';
ORACLE instance started.
Total System Global Area 97591104 bytes
Fixed Size 454464 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
SQL>
During
nomount mode Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured. No datafile and other structure are checked in that moment. As you can see instance is started without problems and all necessary parameters are set automatically according Oracle version you are deploying.
SQL> show sga
Total System Global Area 97591104 bytes
Fixed Size 454464 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
SQL>
Beside that, this Oracle instance can be started and operate as well, I'll rather define all helpful parameters that will make more easy RMAN restore procedure. So here is the whole
initDB1.ora file after manual editing and inserting other values:
*.background_dump_dest='G:\admin\DB1\bdump'
*.compatible='9.2.0.0.1'
*.control_file_record_keep_time=31
*.control_files='G:\oradata\DB1\control01.ctl','G:\oradata\DB1\control02.ctl'
*.core_dump_dest='G:\admin\DB1\cdump'
*.db_block_size=4096
*.db_cache_size=209715200
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DB1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB1XDB)'
*.dml_locks=200
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='DB1'
*.java_pool_size=0
*.large_pool_size=0
*.log_archive_dest='G:\oradata\DB1\archive'
*.log_archive_format='arch%S.arc'
*.log_archive_start=TRUE
*.log_buffer=524288
*.open_cursors=1500
*.optimizer_mode='CHOOSE'
*.pga_aggregate_target=209715200
*.processes=100
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=262144000
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='G:\admin\DB1\udump'
*.utl_file_dir='*'
Restart instance to activate new parameters:
startup force nomount pfile='g:\initDB1.ora';
Restore controlfile
After database is started in
nomount mode with expanded set of parameters, restoring controlfile is next vital step. In mine case client was having correct (original) directory location for RMAN backup. RMAN backup structure is fixable on any Linux environment and a little bit harder to maintain on Windows environments, especially in a cases that backup directory doesn't exist (there is no E disk for instance!). Hopefully this was not mine case.
More about RMAN directory problem may be read in one of mine previous topics
Curious RMAN-05001 in database duplicate. After I copied files from tape (with NT backup/restore program) I was ready to start restore process.
Here is dir command output from RMAN backup directory:
e:\BACKUP\ORACLE>dir
Volume in drive E is New Volume
Volume Serial Number is C036-BB17
Directory of e:\BACKUP\ORACLE
10.08.2009 17:20 <DIR> .
10.08.2009 17:20 <DIR> ..
14.09.2010 16:04 12.238.848 CF_C-4204131874-20100914-00
14.03.2011 10:11 <DIR> CTL
14.09.2010 15:55 345.367.552 DB1_6545_1.BAK
14.09.2010 16:04 7.060.422.656 DB1_6546_1.BAK
14.09.2010 16:04 257.536 DB1_6547_1.BAK
14.03.2011 10:11 <DIR> LOG
14.09.2010 16:04 12.226.560 SNAPCF_DB1
e:\BACKUP\ORACLE>
It is more then obvious that control file is located in
CF_C-4204131874-20100914-00 file (default format is CF_%F). File name say to us it is created 14th September 2010, what also might help in some situations.
All RMAN actions will be started with this command so I'll reference them just once:
g:\>rman target / nocatalog
Recovery Manager: Release 9.2.0.6.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DB1 (not mounted)
using target database controlfile instead of recovery catalog
RMAN>
After logged in to RMAN perform next statement:
run {
set controlfile autobackup format for device type disk to 'e:\BACKUP\ORACLE\CF_%F';
allocate channel c1 type disk;
restore controlfile from 'e:\BACKUP\ORACLE\CF_C-4204131874-20100914-00';
}
Succesfull output can be seen here bellow:
RMAN> run {
2> set controlfile autobackup format for device type disk to 'e:\BACKUP\ORACLE\CF_%F';
3> allocate channel c1 type disk;
4> restore controlfile from 'e:\BACKUP\ORACLE\CF_C-4204131874-20100914-00';
5> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=12 devtype=DISK
Starting restore at 18.03.11
channel c1: restoring controlfile
channel c1: restore complete
replicating controlfile
input filename=G:\ORADATA\DB1\CONTROL01.CTL
output filename=G:\ORADATA\DB1\CONTROL02.CTL
Finished restore at 18.03.11
released channel: c1
RMAN>
Let us check directory where control files are restored:
G:\oradata\DB1>dir
Volume in drive G is DB1_RESTORE
Volume Serial Number is C88A-D7F0
Directory of G:\oradata\DB1
18.03.2011 22:25 <DIR> .
18.03.2011 22:25 <DIR> ..
15.09.2010 17:36 <DIR> archive
18.03.2011 22:26 12.226.560 CONTROL01.CTL
18.03.2011 22:26 12.226.560 CONTROL02.CTL
15.09.2010 17:37 <DIR> log
2 File(s) 24.453.120 bytes
4 Dir(s) 99.140.263.936 bytes free
G:\oradata\DB1>
So far so good.
Now is time to place database in
mount mode (RMAN or sqlplus):
RMAN> alter database mount;
database mounted
RMAN>
Restoring redolog files
Placing database in mount state allow us to query database against Oracle
v$ and
x$ (fixed) views as well as to manipulate with all datafiles.
This allow us to see information about redolog files. This is done with well known statement (from sqlplus):
SQL> col member format a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE D:\DB1\ORACLE\LOG\REDO1_1.LOG
1 ONLINE E:\DB1\ORACLE\LOG\REDO1_1.LOG
2 ONLINE D:\DB1\ORACLE\LOG\REDO1_2.LOG
2 ONLINE E:\DB1\ORACLE\LOG\REDO1_2.LOG
3 ONLINE D:\DB1\ORACLE\LOG\REDO1_3.LOG
3 ONLINE E:\DB1\ORACLE\LOG\REDO1_3.LOG
4 ONLINE D:\DB1\ORACLE\LOG\REDO1_4.LOG
4 ONLINE E:\DB1\ORACLE\LOG\REDO1_4.LOG
8 rows selected.
Our original DB1 database had 4 groups with two members in each group. Because each group members has same name, they should be stored in different directories. Because we will restore all files under "G:\ORADATA\DB1" directory, we have to rename redolog files. For that, starting point is to use next script:
SELECT 'SQL "ALTER DATABASE RENAME FILE '||
''''|| '''' || member || '''' || ''''||
' TO ' ||
''''|| '''' ||'G:\ORADATA\DB1\'||
SUBSTR(member, INSTR(member,'\', -1, 1)+1)||''''||
''' " ;' RMAN_COMMAND
FROM v$logfile
ORDER BY member;
Script result should be slightly changed in part for "\LOG\" subdirectory. In a case there was no more then one member per group, result is ready to use. In this case, edited and final result should look like :
run {
SQL "ALTER DATABASE RENAME FILE ''D:\DB1\ORACLE\LOG\REDO1_1.LOG'' TO ''G:\ORADATA\DB1\REDO1_1.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\DB1\ORACLE\LOG\REDO1_1.LOG'' TO ''G:\ORADATA\DB1\LOG\REDO1_1.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''D:\DB1\ORACLE\LOG\REDO1_2.LOG'' TO ''G:\ORADATA\DB1\REDO1_2.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\DB1\ORACLE\LOG\REDO1_2.LOG'' TO ''G:\ORADATA\DB1\LOG\REDO1_2.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''D:\DB1\ORACLE\LOG\REDO1_3.LOG'' TO ''G:\ORADATA\DB1\REDO1_3.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\DB1\ORACLE\LOG\REDO1_3.LOG'' TO ''G:\ORADATA\DB1\LOG\REDO1_3.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''D:\DB1\ORACLE\LOG\REDO1_4.LOG'' TO ''G:\ORADATA\DB1\REDO1_4.LOG'' ";
SQL "ALTER DATABASE RENAME FILE ''E:\DB1\ORACLE\LOG\REDO1_4.LOG'' TO ''G:\ORADATA\DB1\LOG\REDO1_4.LOG'' ";
}
After successful execution (there would be no errors at all), when we query (from sqlplus) again
v$logfile view, we have next result:
SQL> col member format a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE G:\ORADATA\DB1\REDO1_1.LOG
1 ONLINE G:\ORADATA\DB1\LOG\REDO1_1.LOG
2 ONLINE G:\ORADATA\DB1\REDO1_2.LOG
2 ONLINE G:\ORADATA\DB1\LOG\REDO1_2.LOG
3 ONLINE G:\ORADATA\DB1\REDO1_3.LOG
3 ONLINE G:\ORADATA\DB1\LOG\REDO1_3.LOG
4 ONLINE G:\ORADATA\DB1\REDO1_4.LOG
4 ONLINE G:\ORADATA\DB1\LOG\REDO1_4.LOG
8 rows selected.
SQL>
As you see all redolog files are now pointing on "G:\ORADATA\DB1" and "G:\ORADATA\DB1\LOG" locations.
If you look on file system you'll not find those files! For now, they only exist in controlfile data and they will be physically created after succesful recover.
Finding correct SCN
In mine previous post
How to find correct SCN? I have desribed how to find minimal SCN which is needed to succesfuly recover database. Here is the output of that script in case of DB1 database:
SQL> /
Instance # File # File name Checkpoint # Checkpoint time Last change # STATUS
---------- -------- ------------------------------ ------------ -------------------- ---------------- -------
1 1 D:\DB1\ORACLE\DBF\DB1_SYS.DBF 746601062 09/14/2010 16:55:32 SYSTEM
1 2 D:\DB1\ORACLE\DBF\UNDOTBS01.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 3 D:\DB1\ORACLE\DBF\DB1_IDX1.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 4 E:\DB1\ORACLE\DBF\DB1_TBL1.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 5 E:\DB1\ORACLE\DBF\DB1_TBL2.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 6 E:\DB1\ORACLE\DBF\DB1_ARCH.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 7 E:\DB1\ORACLE\DBF\ARCHIVE.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 8 E:\DB1\ORACLE\DBF\DB1_APP.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 9 E:\DB1\ORACLE\DBF\DB1_CLAIM.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 10 E:\DB1\ORACLE\DBF\DB1_POS.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 11 E:\DB1\ORACLE\DBF\DB1_SUB1.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 12 E:\DB1\ORACLE\DBF\DB1_SUB2.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 13 E:\DB1\ORACLE\DBF\DB1_IMG1.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 14 E:\DB1\ORACLE\DBF\DB1_IMG2.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 15 E:\DB1\ORACLE\DBF\DB1_MOC.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 16 E:\DB1\ORACLE\DBF\DB1_PTM.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 17 E:\DB1\ORACLE\DBF\DB1_REC.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 18 E:\DB1\ORACLE\DBF\DB1_ZMM.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 19 D:\DB1\ORACLE\DBF\DB1_IMG_IDX.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 20 D:\DB1\ORACLE\DBF\DB1_POS_IDX.DBF 746601062 09/14/2010 16:55:32 ONLINE
1 21 D:\DB1\ORACLE\DBF\DB1_SUB_IDX.DBF 746601062 09/14/2010 16:55:32 ONLINE
21 rows selected.
SQL>
Last SCN is
746601062 so our SCN should be
one bigger, what is 746601063.
Restore/recover database
Restore and recover should go in this moment pretty smooth because all important thisngs are passed. All now is relaying on correct backup (that all necessary files are available). Action is divided in following 5 steps:
- set until
- set newname (alow us to restore datafile to new location)
- restore
- switch datafile (write information to controlfile
- recover
Firstly we have to generate "SET NEW NAME" script for RMAN (to avoid hand made script). For that I use next SQL script:
set pagesize 2000
set linesize 160
set head off
set verify off
SELECT 'SET NEWNAME FOR DATAFILE '||
fe.fenum ||
' TO '||
''''||'G:\ORADATA\DB1\'||
SUBSTR(fn.fnnam, INSTR(fn.fnnam,'\', -1, 1)+1)||
'''' RMAN_COMMAND
FROM x$kccfe fe,
x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )
OR (fe.fepax = 65535 OR fe.fepax = 0)
)
AND fn.fnfno = fe.fenum
AND fe.fefnh = fn.fnnum
AND fe.fedup != 0
AND fn.fntyp = 4
AND fn.fnnam IS NOT NULL
AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;
Output is like:
SQL> SELECT 'SET NEWNAME FOR DATAFILE '||
2 fe.fenum ||
3 ' TO '||
4 ''''||'G:\ORADATA\DB1\'||
5 SUBSTR(fn.fnnam, INSTR(fn.fnnam,'\', -1, 1)+1)||
6 ''';' RMAN_COMMAND
7 FROM x$kccfe fe,
8 x$kccfn fn
9 WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )
10 OR (fe.fepax = 65535 OR fe.fepax = 0)
11 )
12 AND fn.fnfno = fe.fenum
13 AND fe.fefnh = fn.fnnum
14 AND fe.fedup != 0
15 AND fn.fntyp = 4
16 AND fn.fnnam IS NOT NULL
17 AND BITAND (fn.fnflg, 4) != 4
18 ORDER BY fe.fenum
19 ;
SET NEWNAME FOR DATAFILE 1 TO 'G:\ORADATA\DB1\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'G:\ORADATA\DB1\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'G:\ORADATA\DB1\DRSYS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'G:\ORADATA\DB1\INDX01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'G:\ORADATA\DB1\TOOLS01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'G:\ORADATA\DB1\USERS01.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'G:\ORADATA\DB1\XDB01.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'G:\ORADATA\DB1\DB1_IDX1.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'G:\ORADATA\DB1\DB1_TBL1.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'G:\ORADATA\DB1\DB1_TBL2.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'G:\ORADATA\DB1\DB1_ARCH.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'G:\ORADATA\DB1\ARCHIVE.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'G:\ORADATA\DB1\DB1_APP.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'G:\ORADATA\DB1\DB1_CLAIM.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'G:\ORADATA\DB1\DB1_POS.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'G:\ORADATA\DB1\DB1_SUB1.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'G:\ORADATA\DB1\DB1_SUB2.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'G:\ORADATA\DB1\DB1_IMG1.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'G:\ORADATA\DB1\DB1_IMG2.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'G:\ORADATA\DB1\DB1_MOC.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'G:\ORADATA\DB1\DB1_PTM.DBF';
21 rows selected.
SQL>
Here is the whole RMAN command:
run {
set until SCN 746601063;
SET NEWNAME FOR DATAFILE 1 TO 'G:\ORADATA\DB1\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'G:\ORADATA\DB1\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'G:\ORADATA\DB1\DRSYS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'G:\ORADATA\DB1\INDX01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'G:\ORADATA\DB1\TOOLS01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'G:\ORADATA\DB1\USERS01.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'G:\ORADATA\DB1\XDB01.DBF';
SET NEWNAME FOR DATAFILE 8 TO 'G:\ORADATA\DB1\DB1_IDX1.DBF';
SET NEWNAME FOR DATAFILE 9 TO 'G:\ORADATA\DB1\DB1_TBL1.DBF';
SET NEWNAME FOR DATAFILE 10 TO 'G:\ORADATA\DB1\DB1_TBL2.DBF';
SET NEWNAME FOR DATAFILE 11 TO 'G:\ORADATA\DB1\DB1_ARCH.DBF';
SET NEWNAME FOR DATAFILE 12 TO 'G:\ORADATA\DB1\ARCHIVE.DBF';
SET NEWNAME FOR DATAFILE 13 TO 'G:\ORADATA\DB1\DB1_APP.DBF';
SET NEWNAME FOR DATAFILE 14 TO 'G:\ORADATA\DB1\DB1_CLAIM.DBF';
SET NEWNAME FOR DATAFILE 15 TO 'G:\ORADATA\DB1\DB1_POS.DBF';
SET NEWNAME FOR DATAFILE 16 TO 'G:\ORADATA\DB1\DB1_SUB1.DBF';
SET NEWNAME FOR DATAFILE 17 TO 'G:\ORADATA\DB1\DB1_SUB2.DBF';
SET NEWNAME FOR DATAFILE 18 TO 'G:\ORADATA\DB1\DB1_IMG1.DBF';
SET NEWNAME FOR DATAFILE 19 TO 'G:\ORADATA\DB1\DB1_IMG2.DBF';
SET NEWNAME FOR DATAFILE 20 TO 'G:\ORADATA\DB1\DB1_MOC.DBF';
SET NEWNAME FOR DATAFILE 21 TO 'G:\ORADATA\DB1\DB1_PTM.DBF';
restore database;
switch datafile all;
recover database;
}
After some time, RMAN finish with no errors. Output in mine case is bellow.
RMAN> run {
2> set until SCN 746601063;
3> SET NEWNAME FOR DATAFILE 1 TO 'G:\ORADATA\DB1\SYSTEM01.DBF';
4> SET NEWNAME FOR DATAFILE 2 TO 'G:\ORADATA\DB1\UNDOTBS01.DBF';
5> SET NEWNAME FOR DATAFILE 3 TO 'G:\ORADATA\DB1\DRSYS01.DBF';
6> SET NEWNAME FOR DATAFILE 4 TO 'G:\ORADATA\DB1\INDX01.DBF';
7> SET NEWNAME FOR DATAFILE 5 TO 'G:\ORADATA\DB1\TOOLS01.DBF';
8> SET NEWNAME FOR DATAFILE 6 TO 'G:\ORADATA\DB1\USERS01.DBF';
9> SET NEWNAME FOR DATAFILE 7 TO 'G:\ORADATA\DB1\XDB01.DBF';
10> SET NEWNAME FOR DATAFILE 8 TO 'G:\ORADATA\DB1\DB1_IDX1.DBF';
11> SET NEWNAME FOR DATAFILE 9 TO 'G:\ORADATA\DB1\DB1_TBL1.DBF';
12> SET NEWNAME FOR DATAFILE 10 TO 'G:\ORADATA\DB1\DB1_TBL2.DBF';
13> SET NEWNAME FOR DATAFILE 11 TO 'G:\ORADATA\DB1\DB1_ARCH.DBF';
14> SET NEWNAME FOR DATAFILE 12 TO 'G:\ORADATA\DB1\ARCHIVE.DBF';
15> SET NEWNAME FOR DATAFILE 13 TO 'G:\ORADATA\DB1\DB1_APP.DBF';
16> SET NEWNAME FOR DATAFILE 14 TO 'G:\ORADATA\DB1\DB1_CLAIM.DBF';
17> SET NEWNAME FOR DATAFILE 15 TO 'G:\ORADATA\DB1\DB1_POS.DBF';
18> SET NEWNAME FOR DATAFILE 16 TO 'G:\ORADATA\DB1\DB1_SUB1.DBF';
19> SET NEWNAME FOR DATAFILE 17 TO 'G:\ORADATA\DB1\DB1_SUB2.DBF';
20> SET NEWNAME FOR DATAFILE 18 TO 'G:\ORADATA\DB1\DB1_IMG1.DBF';
21> SET NEWNAME FOR DATAFILE 19 TO 'G:\ORADATA\DB1\DB1_IMG2.DBF';
22> SET NEWNAME FOR DATAFILE 20 TO 'G:\ORADATA\DB1\DB1_MOC.DBF';
23> SET NEWNAME FOR DATAFILE 21 TO 'G:\ORADATA\DB1\DB1_PTM.DBF';
24> restore database;
25> switch datafile all;
26> recover database;
27> }
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18.03.11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to G:\ORADATA\DB1\SYSTEM01.DBF
restoring datafile 00002 to G:\ORADATA\DB1\UNDOTBS01.DBF
restoring datafile 00003 to G:\ORADATA\DB1\DRSYS01.DBF
restoring datafile 00004 to G:\ORADATA\DB1\INDX01.DBF
restoring datafile 00005 to G:\ORADATA\DB1\TOOLS01.DBF
restoring datafile 00006 to G:\ORADATA\DB1\USERS01.DBF
restoring datafile 00007 to G:\ORADATA\DB1\XDB01.DBF
restoring datafile 00008 to G:\ORADATA\DB1\DB1_IDX1.DBF
restoring datafile 00009 to G:\ORADATA\DB1\DB1_TBL1.DBF
restoring datafile 00010 to G:\ORADATA\DB1\DB1_TBL2.DBF
restoring datafile 00011 to G:\ORADATA\DB1\DB1_ARCH.DBF
restoring datafile 00012 to G:\ORADATA\DB1\ARCHIVE.DBF
restoring datafile 00013 to G:\ORADATA\DB1\DB1_APP.DBF
restoring datafile 00014 to G:\ORADATA\DB1\DB1_CLAIM.DBF
restoring datafile 00015 to G:\ORADATA\DB1\DB1_POS.DBF
restoring datafile 00016 to G:\ORADATA\DB1\DB1_SUB1.DBF
restoring datafile 00017 to G:\ORADATA\DB1\DB1_SUB2.DBF
restoring datafile 00018 to G:\ORADATA\DB1\DB1_IMG1.DBF
restoring datafile 00019 to G:\ORADATA\DB1\DB1_IMG2.DBF
restoring datafile 00020 to G:\ORADATA\DB1\DB1_MOC.DBF
restoring datafile 00021 to G:\ORADATA\DB1\DB1_PTM.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\ORACLE\DB1_6546_1.BAK tag=TAG20100914T165531 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 19.03.11
datafile 1 switched to datafile copy
input datafilecopy recid=22 stamp=746150571 filename=G:\ORADATA\DB1\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafilecopy recid=23 stamp=746150571 filename=G:\ORADATA\DB1\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=24 stamp=746150571 filename=G:\ORADATA\DB1\DRSYS01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=25 stamp=746150572 filename=G:\ORADATA\DB1\INDX01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=26 stamp=746150572 filename=G:\ORADATA\DB1\TOOLS01.DBF
datafile 6 switched to datafile copy
input datafilecopy recid=27 stamp=746150573 filename=G:\ORADATA\DB1\USERS01.DBF
datafile 7 switched to datafile copy
input datafilecopy recid=28 stamp=746150573 filename=G:\ORADATA\DB1\XDB01.DBF
datafile 8 switched to datafile copy
input datafilecopy recid=29 stamp=746150573 filename=G:\ORADATA\DB1\DB1_IDX1.DBF
datafile 9 switched to datafile copy
input datafilecopy recid=30 stamp=746150574 filename=G:\ORADATA\DB1\DB1_TBL1.DBF
datafile 10 switched to datafile copy
input datafilecopy recid=31 stamp=746150574 filename=G:\ORADATA\DB1\DB1_TBL2.DBF
datafile 11 switched to datafile copy
input datafilecopy recid=32 stamp=746150574 filename=G:\ORADATA\DB1\DB1_ARCH.DBF
datafile 12 switched to datafile copy
input datafilecopy recid=33 stamp=746150575 filename=G:\ORADATA\DB1\ARCHIVE.DBF
datafile 13 switched to datafile copy
input datafilecopy recid=34 stamp=746150575 filename=G:\ORADATA\DB1\DB1_APP.DBF
datafile 14 switched to datafile copy
input datafilecopy recid=35 stamp=746150575 filename=G:\ORADATA\DB1\DB1_CLAIM.DBF
datafile 15 switched to datafile copy
input datafilecopy recid=36 stamp=746150576 filename=G:\ORADATA\DB1\DB1_POS.DBF
datafile 16 switched to datafile copy
input datafilecopy recid=37 stamp=746150576 filename=G:\ORADATA\DB1\DB1_SUB1.DBF
datafile 17 switched to datafile copy
input datafilecopy recid=38 stamp=746150576 filename=G:\ORADATA\DB1\DB1_SUB2.DBF
datafile 18 switched to datafile copy
input datafilecopy recid=39 stamp=746150577 filename=G:\ORADATA\DB1\DB1_IMG1.DBF
datafile 19 switched to datafile copy
input datafilecopy recid=40 stamp=746150577 filename=G:\ORADATA\DB1\DB1_IMG2.DBF
datafile 20 switched to datafile copy
input datafilecopy recid=41 stamp=746150577 filename=G:\ORADATA\DB1\DB1_MOC.DBF
datafile 21 switched to datafile copy
input datafilecopy recid=42 stamp=746150578 filename=G:\ORADATA\DB1\DB1_PTM.DBF
Starting recover at 19.03.11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11686
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\ORACLE\DB1_6547_1.BAK tag=TAG20100914T170448 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=G:\ORADATA\DB1\ARCHIVE\ARCH11686.ARC thread=1 sequence=11686
media recovery complete
Finished recover at 19.03.11
RMAN>
Errors in alert.log
While RMAN restore is running, if you look in database alert log file, you might get frighten finding errors like:
Fri Mar 18 23:13:12 2011
Errors in file g:\admin\DB1\bdump\db1_dbw0_4020.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'E:\DB1\ORACLE\DBF\DB1_POS.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
This is normal and should be treated more like warning but error. The reason is that controlfile has still information for old directory location, which will be updated after execution of
switch datafile all; command in RMAN script.
Opening database
Only thing that is left is to do is to open database with
resetlogs option (RMAN or sqlplus).
RMAN> alter database open resetlogs;
database opened
RMAN>
Now redolog files should be shown as well!
:-)
The End
Situation like explained is something that should not ever happened but from time to time, me or mine colleagues hear or face situation like explained. So I tried to show mine approach in such a situations. Hope that this will help someone.
Before last regard, didn't you forget something? No?
Immediately backup the new database and make some db diary to save all important data about it!
:-)
Cheers!
