One of live recovery scenario faced by our DBA
ONE OF THE DATABASE PROD (DB NAME
CHANGED) WHICH HAS 12 DATAFILES MISSING. We came to know about these 12 datafiles from
the Alert logfile and from the v$datafile view (status='RECOVER').
We had this power outage on Sunday, for all the three databases we had a successful
RMAN tape hot backup on Saturday (Evident from the RMAN logfiles).
To check if the backup is physically available in the backup silo (We have about 800
Production databases and all the backups go to the one silo) we gave the below command
but it was hanging and never returned output.
RMAN> list backup;
Then we gave the below command to find the availability of backupset of datafile '4' ('4'
is the file_id of one of the missing datafile of PROD database) that is missing.
RMAN> LIST BACKUPSET OF DATAFILE 4;
The above command showed the datafile id '4' is available in the backup. Then we gave
the below command to restore and recover the datafile 4; Then it failed with the below
error saying that its didn't have archivelog file with sequence 185233 to restore.
RMAN> Connect target / ;
RMAN> Connect catalog rman/rman@PROD
RMAN> startup mount;
RMAN>run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
release channel t1;
}
allocated channel: t1
channel t1: sid=25 devtype=SBT_TAPE
channel t1: XXX v4.2.0.0
Starting restore at 27-APR-09
released channel: t1
RMAN-00571:===================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571:===================================================
RMAN-03002: failure of restore command at 04/27/2009 12:43:00
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 185233 scn 6111805242226 found to
restore
RMAN> EXIT;
Then we checked the RMAN logfiles, they showed that all the archivelogs have been
backed up successfully. We then listed the archivelog file that was missing and it showed
as expired.
RMAN> LIST BACKUPSET OF archivelog sequence 185233;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
39383086 269M SBT_TAPE 00:00:41 26-APR-09
BP Key: 39383088 Status: EXPIRED Tag: TAG20090426T005740
Piece Name: offsite_arch_PROD_S187166_685155460
List of Archived Logs in backup set 39383086
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 185233 6111805242226 25-APR-09 6111807600250 26-APR-09
Then we contacted the storage group about the missing archivelog file and they
confirmed that they see the backupset 39383086 as available in the silo, So we went with
the "CROSS CHECK" command as below. Now the RMAN has realized that the
archivelog is available in the silo it changed its status to "AVAILABLE"
RMAN>ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE 'sbt_tape'
parms 'ENV=(XXX_SERVER=xxhaxx)';
RMAN>CROSSCHECK BACKUPSET 39383086;
RMAN> LIST BACKUPSET OF archivelog sequence 185233;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
39383086 269M SBT_TAPE 00:00:41 26-APR-09
BP Key: 39383088 Status: AVAILABLE Tag: TAG20090426T005740
Piece Name: offsite_arch_PROD_S187166_685155460
List of Archived Logs in backup set 39383086
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 185233 6111805242226 25-APR-09 6111807600250 26-APR-09
We followed the same method for making all the archivelog files that couldn't be
restored. Then we gave the restore and recovery command for datafile '4' and this time it
was successful. It restored the datafile '4' and all the archivelogfiles that are recovered for
the recovery.
RMAN> Connect target / ;
RMAN> Connect catalog rman/rman@PROD
RMAN>run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
release channel t1;
}
RMAN> Exit;
Now we are sure that everything is working fine and went for the recovery of the
remaining 11 datafiles as below. This time RMAN restored only the datafiles but not the
archivelogfiles as all the archivelogfiles required for the recovery were already restored.
We used multiple channels to improve the performance of restore and recovery. The
restore and recovery of 11 datafiles finished in about 2 Hr's.
RMAN> Connect target / ;
RMAN> Connect catalog rman/rman@PROD
RMAN>run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
allocate channel t3 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
allocate channel t4 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
allocate channel t5 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
allocate channel t6 type 'sbt_tape' parms 'ENV=(XXX_SERVER=xxhaxx)';
sql 'alter database datafile 16 offline';
sql 'alter database datafile 9 offline';
sql 'alter database datafile 18 offline';
sql 'alter database datafile 19 offline';
sql 'alter database datafile 23 offline';
sql 'alter database datafile 24 offline';
sql 'alter database datafile 46 offline';
sql 'alter database datafile 47 offline';
sql 'alter database datafile 66 offline';
sql 'alter database datafile 67 offline';
sql 'alter database datafile 79 offline';
restore datafile 16,9,18,19,23,24,46,47,66,67,79;
recover datafile 16,9,18,19,23,24,46,47,66,67,79;
sql 'alter database datafile 16 online';
sql 'alter database datafile 9 online';
sql 'alter database datafile 18 online';
sql 'alter database datafile 19 online';
sql 'alter database datafile 23 online';
sql 'alter database datafile 24 online';
sql 'alter database datafile 46 online';
sql 'alter database datafile 47 online';
sql 'alter database datafile 66 online';
sql 'alter database datafile 67 online';
sql 'alter database datafile 79 online';
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
}
RMAN>sql 'alter database open';
RMAN>EXIT;
Author – A.Kishore
http://appsdba.info
How to Recover lost online redo logs ?
If you lose the current online redo log, then you will not be able to recover the
information in that online redo log. This is one reason why redo logs should be
multiplexed. If it is multiplexed, you will have a copy of the online redo log. Let's assume
that your online redo log group #1 has two members, redo01a.log and redo01b.log. If
redo01a.log is missing, simply shutdown the database and copy redo01b.log and rename
it to redo01a.log. You should be able to start the database.
If you have not multiplexed your online redo logs, then you are only left with incomplete
recovery. Your steps are as follows:
1. SHUTDOWN ABORT
2. STARTUP MOUNT
3. RECOVER DATABASE UNTIL CANCEL;
4. When you have applied any archived redo logs, then reply CANCEL to stop the
recovery process.
5. ALTER DATABASE OPEN RESETLOGS;
The last step will recreate your missing log files. Any transactions in those missing log
files that were not written to disk are now lost. Any time you open with RESETLOGS,
make sure you shutdown the database and take a good backup.
If it is multiplexed then replace the lost one with the available one else restore it from
backup. Here I am using RMAN
Note : RMAN will never take the backup of RMAN logs
RMAN SID = RECO
TARGET SID = TEST5
Check that our target system is running on archive log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
Author – A.Kishore
http://appsdba.info
RMAN Setup and Configuration
----------------------------
Configure the Database for RMAN Operations
Set Up the Database User in the target database - on the TEST database
create user backup_admin identified by backup_admin default tablespace users;
grant sysdba to backup_admin;
Creating the Recovery Catalog User - on RECO database
create user rcat_user identified by rcat_user default tablespace users;
grant connect,resource,recovery_catalog_owner to rcat_user;
Creating the Recovery Catalog Schema Objects
Step 1. Connect to the recover catalog with RMAN:
rman catalog=rcat_user/rcat_user@reco
Step 2. Issue the create catalog command from the RMAN prompt:
create catalog;
Register your database in the recovery catalog
Step 1: Using RMAN, sign into the database and the recover catalog at the same time
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test5
Step 2: Register the database with the recovery catalog
RMAN> register database
RMAN> show all;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;
Author – A.Kishore
http://appsdba.info
run
{
backup database plus archivelog;
backup current controlfile;
}
-- Add some records
SQL> insert into test values(3);
1 row created.
SQL> commit;
Commit complete.
C:\>sqlplus scott/tiger@test5
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 22 15:00:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning, OLAP and Data Mining options
C:\>sqlplus "sys/oracle@test5 as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 22 15:00:05 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Author – A.Kishore
http://appsdba.info
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 79693180 bytes
Database Buffers 79691776 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>
C:\>rman catalog=rcat_user/rcat_user@RECO
target=backup_admin/backup_admin@test5
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 22 13:41:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST5 (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 22-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\AUTOBACKUP
\2009_04_22\O1_MF_S_684
859432_4YZ3WTC8_.BKP
Author – A.Kishore
http://appsdba.info
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\AUTOB
ACKUP\2009_04_22\O1_MF_S_684859432_4YZ3WTC8_.BKP tag=TAG20090
422T144352
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL01.CTL
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL02.CTL
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL03.CTL
Finished restore at 22-APR-09
RMAN> restore database;
Starting restore at 22-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\SYSTEM01.DBF
restoring datafile 00002 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\UNDOTBS01.DBF
restoring datafile 00003 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\SYSAUX01.DBF
restoring datafile 00004 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\USERS01.DBF
restoring datafile 00005 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\BACKUPSET\2
009_04_22\O1_MF_NNNDF_
TAG20090422T144216_4YZ3SSFN_.BKP
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\BACKU
PSET\2009_04_22\O1_MF_NNNDF_TAG20090422T144216_4YZ3SSFN_.BKP
tag=TAG20090422T144216
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 22-APR-09
Author – A.Kishore
http://appsdba.info
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database until logseq 5;
Starting recover at 22-APR-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:06
Finished recover at 22-APR-09
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
SQL> connect scott/tiger@test5
Connected.
SQL> select * from test;
T
----------
1
2
The data stored in the redo log file is lost, that is reason we should always multiplex the
redo logfile
Author – A.Kishore
http://appsdba.info
How to Recover the lost current control file, or the current control file is
inconsistent with files that you need to recover??
If it is multiplexed then replace the lost one with the available one else restore it from
backup. Here I am using RMAN
RMAN SID = RECO
TARGET SID = TEST5
Check that our target system is running on archive log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
RMAN Setup and Configuration
----------------------------
Configure the Database for RMAN Operations
Set Up the Database User in the target database - on the TEST database
create user backup_admin identified by backup_admin default tablespace users;
grant sysdba to backup_admin;
Creating the Recovery Catalog User - on RECO database
create user rcat_user identified by rcat_user default tablespace users;
grant connect,resource,recovery_catalog_owner to rcat_user;
Creating the Recovery Catalog Schema Objects
Step 1. Connect to the recover catalog with RMAN:
rman catalog=rcat_user/rcat_user@reco
Author – A.Kishore
http://appsdba.info
Step 2. Issue the create catalog command from the RMAN prompt:
create catalog;
Register your database in the recovery catalog
Step 1: Using RMAN, sign into the database and the recover catalog at the same time
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test5
Step 2: Register the database with the recovery catalog
RMAN> register database
RMAN> show all;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;
run
{
backup database plus archivelog;
backup current controlfile;
}
-- Add some records
SQL> connect scott/tiger@test5
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password:
Retype new password:
Password changed
Connected.
SQL> create table test(t number);
Table created.
SQL> insert into test values(1);
1 row created.
Author – A.Kishore
http://appsdba.info
SQL> commit;
Commit complete.
SQL> shutdown abort
remove all the controlfiles
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 79693180 bytes
Database Buffers 79691776 bytes
Redo Buffers 7139328 bytes
ORA-00205: error in identifying control file, check alert log for more info
C:\>rman catalog=rcat_user/rcat_user@RECO
target=backup_admin/backup_admin@test5
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 22 13:41:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST5 (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 22-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\AUTOBACKUP
\2009_04_22\O1_MF_S_684
855297_4YYZVMN3_.BKP
Author – A.Kishore
http://appsdba.info
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST5\AUTOB
ACKUP\2009_04_22\O1_MF_S_684855297_4YYZVMN3_.BKP tag=TAG20090
422T133457
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL01.CTL
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL02.CTL
output
filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST5\CONTROL03.CTL
Finished restore at 22-APR-09
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
C:\>sqlplus scott/tiger@test5
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 22 13:57:52 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Author – A.Kishore
http://appsdba.info
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST
SQL> select * from test;
T
----------
1
2
http://www.orafaq.com/wiki/index.php?title=Control_file_recovery&action=edit§ion
=2
Author – A.Kishore
http://appsdba.info
Insanity: doing the same thing over and over again and expecting different results. – Nice
Proverb
No back up was taken after reset logs. How to recover the database?
Solution – It’s possible, I have tried with Oracle 10g. Before Oracle 10g we may have to
reset the incarnation, then we should be able to recover the database.
RMAN SID = RECO
TARGET SID = TEST
Check that our target system is running on archive log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
RMAN Setup and Configuration
----------------------------
Configure the Database for RMAN Operations
Set Up the Database User in the target database - on the TEST database
create user backup_admin identified by backup_admin default tablespace users;
Spongebob - Today I am going to
teach restoration of database
Patrick – Show me how! Show
me how!
Author – A.Kishore
http://appsdba.info
grant sysdba to backup_admin;
Creating the Recovery Catalog User - on RECO database
create user rcat_user identified by rcat_user default tablespace users;
grant connect,resource,recovery_catalog_owner to rcat_user;
Creating the Recovery Catalog Schema Objects
Step 1. Connect to the recover catalog with RMAN:
rman catalog=rcat_user/rcat_user@reco
Step 2. Issue the create catalog command from the RMAN prompt:
create catalog;
Register your database in the recovery catalog
Step 1: Using RMAN, sign into the database and the recover catalog at the same time
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test
Step 2: Register the database with the recovery catalog
RMAN> register database
Take the backup
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test
RMAN> backup database plus archivelog;
Author – A.Kishore
http://appsdba.info
SQL> create table test( t number);
Table created.
SQL> set time on
09:47:27 SQL>
09:47:28 SQL> insert into test values(1);
1 row created.
09:47:42 SQL> commit;
Commit complete.
09:47:45 SQL>
09:47:46 SQL>
09:47:46 SQL>
09:47:46 SQL> create table test1(t number);
Table created.
09:47:56 SQL> insert into test1 values(2);
1 row created.
09:48:05 SQL> commit;
Commit complete.
09:49:04 SQL> drop table test;
Table dropped.
09:49:29 SQL> drop table test1;
Table dropped.
Let’s recover our database – 9:48:20
Author – A.Kishore
http://appsdba.info
n Check the current incarnation of the database
C:\>rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Apr 17 09:59:21 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1982397231)
connected to recovery catalog database
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 8 TEST 1982397231 PARENT 1 30-AUG-05
1 2 TEST 1982397231 CURRENT 534907 15-APR-09
Steps – TEST DB
Shutdown the database
Start the database in mount stage
09:59:30 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:00:20 SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
Steps – RMAN
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test
restore controlfile from autobackup;
restore database;
recover database until time "to_date('04/17/09 9:48:20','MM/DD/YY HH24:MI:SS')";
alter database open resetlogs
C:\>rman catalog=rcat_user/rcat_user@RECO
target=backup_admin/backup_admin@test
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Apr 17 10:04:14 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Author – A.Kishore
http://appsdba.info
connected to target database: TEST (not mounted)
connected to recovery catalog database
RMAN> restore controlfile from autobackup;
Starting restore at 17-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\
2009_04_17\O1_MF_S_684409406_4Y
KDFJFL_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL03.CTL
Finished restore at 17-APR-09
RMAN> restore database;
Starting restore at 17-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
restoring datafile 00005 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF
restoring datafile 00006 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\MYTEST01.DBF
Author – A.Kishore
http://appsdba.info
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\20
09_04_17\O1_MF_NNNDF_T
AG20090417T094124_4YKD9P5Q_.BKP
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUP
SET\2009_04_17\O1_MF_NNNDF_TAG20090417T094124_4YKD9P5Q_.BKP t
ag=TAG20090417T094124
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 17-APR-09
RMAN> recover database until time "to_date('04/17/09 9:48:20','MM/DD/YY
HH24:MI:SS')";
Starting recover at 17-APR-09
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 04/17/2009 10:13:09
ORA-01507: database not mounted
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database until time "to_date('04/17/09 9:48:20','MM/DD/YY
HH24:MI:SS')";
Starting recover at 17-APR-09
Starting implicit crosscheck backup at 17-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 17-APR-09
Starting implicit crosscheck copy at 17-APR-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-APR-09
Author – A.Kishore
http://appsdba.info
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\
2009_04_17\O1_MF_S_684409406_4YKDFJFL_.BKP
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 19 is already on disk as file
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2
009_04_1
7\O1_MF_1_19_4YKDF9H0_.ARC
archive log thread 1 sequence 20 is already on disk as file
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG
archive log
filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHI
VELOG\2009_04_17\O1_MF_1_19_4YKDF9H0_.ARC thread=1 seq
uence=19
archive log filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG
thread=1 sequence=20
media recovery complete, elapsed time: 00:00:07
Finished recover at 17-APR-09
RMAN> alter database open resetlogs;
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 8 TEST 1982397231 PARENT 1 30-AUG-05
1 2 TEST 1982397231 PARENT 534907 15-APR-09
1 1478 TEST 1982397231 CURRENT 647072 17-APR-09
Author – A.Kishore
http://appsdba.info
Recovery completed:
10:17:08 SQL> connect scott/tiger@test
Connected.
10:17:20 SQL> select * from test;
T
----------
1
2
10:17:24 SQL> select * from test1;
no rows selected
NOTE:
The resetlogs option used to open the database in the above example, will create a new
incarnation of the database. It is critical to take a complete backup of the database after
performing a resetlogs.
Spongebob as usual forgot to take the backup after resetlogs
10:17:28 SQL> create table test2(t number);
Table created.
10:30:19 SQL> insert into test2 values(1);
1 row created.
Patrick, see I have
restored the
database. Hi He He
Author – A.Kishore
http://appsdba.info
10:30:26 SQL> commit;
Commit complete.
10:30:29 SQL>
10:30:29 SQL>
10:30:30 SQL>
10:30:30 SQL>
10:30:55 SQL> drop table test2;
Table dropped.
Lets try to restore the database till 10:30:30
09:59:30 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:00:20 SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 75498876 bytes
Database Buffers 83886080 bytes
Redo Buffers 7139328 bytes
Steps – RMAN
rman catalog=rcat_user/rcat_user@RECO target=backup_admin/backup_admin@test
restore controlfile from autobackup;
restore database;
alter database mount;
recover database until time "to_date('04/17/09 10:30:30','MM/DD/YY HH24:MI:SS')";
alter database open resetlogs
C:\>rman catalog=rcat_user/rcat_user@RECO
target=backup_admin/backup_admin@test
Patric .. I will restore
without taking the
backups after the reset
Author – A.Kishore
http://appsdba.info
SQL> connect scott/tiger@test
Connected.
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
TEST
TEST1
TEST2
7 rows selected.
SQL> select * from test2;
T
----------
1
C:\>
C:\>
C:\>rman catalog=rcat_user/rcat_user@RECO
target=backup_admin/backup_admin@test
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Apr 17 14:37:08 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (not mounted)
connected to recovery catalog database
RMAN> restore controlfile from autobackup;
Starting restore at 17-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
Author – A.Kishore
http://appsdba.info
channel ORA_DISK_1: autobackup found:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\
2009_04_17\O1_MF_N_684411334_4Y
KG9ROR_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL03.CTL
Finished restore at 17-APR-09
RMAN> restore database;
Starting restore at 17-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
restoring datafile 00005 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF
restoring datafile 00006 to
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\MYTEST01.DBF
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\20
09_04_17\O1_MF_NNNDF_T
AG20090417T094124_4YKD9P5Q_.BKP
channel ORA_DISK_1: restored backup piece 1
piece
handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUP
SET\2009_04_17\O1_MF_NNNDF_TAG20090417T094124_4YKD9P5Q_.BKP t
ag=TAG20090417T094124
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 17-APR-09
RMAN> recover database until time "to_date('04/17/09 10:30:30','MM/DD/YY
HH24:MI:SS')";
Author – A.Kishore
http://appsdba.info
Starting recover at 17-APR-09
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 04/17/2009 14:39:38
ORA-01507: database not mounted
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database until time "to_date('04/17/09 10:30:30','MM/DD/YY
HH24:MI:SS')";
Starting recover at 17-APR-09
Starting implicit crosscheck backup at 17-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 17-APR-09
Starting implicit crosscheck copy at 17-APR-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-APR-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\
2009_04_17\O1_MF_N_684411334_4YKG9ROR_.BKP
using channel ORA_DISK_1
starting media recovery
Author – A.Kishore
http://appsdba.info
archive log thread 1 sequence 19 is already on disk as file
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2
009_04_1
7\O1_MF_1_19_4YKG8MKT_.ARC
archive log thread 1 sequence 20 is already on disk as file
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2
009_04_1
7\O1_MF_1_20_4YKG8FJV_.ARC
archive log thread 1 sequence 1 is already on disk as file
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG
archive log
filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHI
VELOG\2009_04_17\O1_MF_1_19_4YKG8MKT_.ARC thread=1 seq
uence=19
archive log
filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHI
VELOG\2009_04_17\O1_MF_1_20_4YKG8FJV_.ARC thread=1 seq
uence=20
archive log filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG
thread=1 sequence=1
media recovery complete, elapsed time: 00:00:10
Finished recover at 17-APR-09
RMAN> alter database open resetlogs
2> /
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signalled during parse
RMAN-02001: unrecognized punctuation symbol "/"
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Author – A.Kishore
http://appsdba.info
SQL> select * from test2;
T
----------
1
http://forums11.itrc.hp.com/service/forums/questionanswer.do?admit=109447626+12399
85178932+28353475&threadId=653460
Before Oracle 10g
Now I configured a recovery catalog on a different system and backup/recovery is succuss.
I could do same recovery multiple times using rman/dp.
To redo the recovery after reset logs,
From RMAN>list incarnation of database;
RMAN>reset database to incarnation <number>;
Then SQL>shutdown immediate;
SQL>startup nomount;
Restored control files only DP GUI.
Restored and recovered from DP/GUI all the items (recover until option).
I could do this multiple times.
So, Maintaining a Recovery catalog look like easy and recommended method.
Thanks for every one again.
I have done it
again …
