Scenario:- So here i am producing a test case where I will need to recover the database but the last archivelog is not present in system which required for the database recovery.
Database Version:- Oracle 11g R2
Database SID:- prim
First before the doing the activity i'am taking a full database backup along with archivelogs.
[oracle@server1 u01]$ rman target sys/sys@prim
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=4130009889)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default
RMAN> backup database plus archivelog;
Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=868489636
input archived log thread=1 sequence=2 RECID=5 STAMP=868489684
input archived log thread=1 sequence=3 RECID=6 STAMP=868490020
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231340_bbxjgdwc_.bkp tag=TAG20150108T231340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15
Starting backup at 08-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T231341_bbxjggsm_.bkp tag=TAG20150108T231341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
Finished backup at 08-JAN-15
Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=7 STAMP=868490079
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231439_bbxjj7oj_.bkp tag=TAG20150108T231439 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15
Starting Control File and SPFILE Autobackup at 08-JAN-15
piece handle=/u01/app/c-4130009889-20150108-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-15
RMAN> exit
Now once the backups have been done, lets make some changes inside the database.
SQL> create user soumya identified by soumya;
User created.
SQL> grant connect , resource to soumya;
Grant succeeded.
SQL> conn soumya/soumya
Connected.
SQL> create table xyz ( id number);
Table created.
SQL> insert into xyz values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xyz;
ID
----------
1
1
1
1
1
Now lets drop the controlfile from system and recover it from the backup.
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall 10076160 Jan 9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan 8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall 52429312 Jan 9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Jan 8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Jan 8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan 9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan 9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall 57679872 Jan 9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 8 23:4
[root@server1 prim]# rm -rf control01.ctl
[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan 9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl
NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba
SQL> alter system switch logfile;
System altered.
Now, lets try to restore the controlfile from autobackup.
Bringing the instance in nomount mode for the recovery purpose.
SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> startup nomount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
[oracle@server1 u01]$ rman target sys/sys@prim
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (not mounted)
RMAN> set dbid=4130009889
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
The above error comes generally in two cases,
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\
flash recovery area location.
So to overcome the above issue we can will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-15
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
[oracle@server1] sqlplus / as sysdba
SQL> alter database open;
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/prim/system01.dbf'
so while opening the database using resetlogs we found the above error.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1032424 generated at 01/08/2015 23:14:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_5_bbxjoph
7_.arc
ORA-00280: change 1032424 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
So when trying to do a cancel based recovery here we see the database report an error that its missing '/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c' archivelog file.
So i checked at os level and couldnt find the archivelog file.
Since we did shutdown immediate, we need to apply the current online redolog when it prompts for.Alternatively, we can supply the names of each of the online redo logs that we have and
Oracle will reject the one(s) that do not correspond to Sequence#6.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 YES ACTIVE 1032376 08-JAN-15 1032415 08-JAN-15
3 1 3 52428800 512 1 YES INACTIVE 1031899 08-JAN-15 1032376 08-JAN-15
2 1 5 52428800 512 1 NO CURRENT 1032415 08-JAN-15 2.8147E+14
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1033111 generated at 01/08/2015 23:17:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00280: change 1033111 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prim/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select * from soumya.xyz;
ID
----------
1
1
1
1
1
So we see we have the table as well which we created after taking the full backup.
Please share your ideas and opinions about this topic.
If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.
Database Version:- Oracle 11g R2
Database SID:- prim
First before the doing the activity i'am taking a full database backup along with archivelogs.
[oracle@server1 u01]$ rman target sys/sys@prim
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=4130009889)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default
RMAN> backup database plus archivelog;
Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=868489636
input archived log thread=1 sequence=2 RECID=5 STAMP=868489684
input archived log thread=1 sequence=3 RECID=6 STAMP=868490020
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231340_bbxjgdwc_.bkp tag=TAG20150108T231340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15
Starting backup at 08-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T231341_bbxjggsm_.bkp tag=TAG20150108T231341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
Finished backup at 08-JAN-15
Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=7 STAMP=868490079
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231439_bbxjj7oj_.bkp tag=TAG20150108T231439 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15
Starting Control File and SPFILE Autobackup at 08-JAN-15
piece handle=/u01/app/c-4130009889-20150108-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-15
RMAN> exit
Now once the backups have been done, lets make some changes inside the database.
SQL> create user soumya identified by soumya;
User created.
SQL> grant connect , resource to soumya;
Grant succeeded.
SQL> conn soumya/soumya
Connected.
SQL> create table xyz ( id number);
Table created.
SQL> insert into xyz values(1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xyz;
ID
----------
1
1
1
1
1
Now lets drop the controlfile from system and recover it from the backup.
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/prim/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/prim/contr
ol02.ctl
[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall 10076160 Jan 9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan 8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall 52429312 Jan 9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Jan 8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Jan 8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan 9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan 9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall 57679872 Jan 9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 8 23:4
[root@server1 prim]# rm -rf control01.ctl
[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan 9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl
NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba
SQL> alter system switch logfile;
System altered.
Now, lets try to restore the controlfile from autobackup.
Bringing the instance in nomount mode for the recovery purpose.
SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> startup nomount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
[oracle@server1 u01]$ rman target sys/sys@prim
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (not mounted)
RMAN> set dbid=4130009889
executing command: SET DBID
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
The above error comes generally in two cases,
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\
flash recovery area location.
So to overcome the above issue we can will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore controlfile from autobackup;
Starting restore at 08-JAN-15
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
[oracle@server1] sqlplus / as sysdba
SQL> alter database open;
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/prim/system01.dbf'
so while opening the database using resetlogs we found the above error.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1032424 generated at 01/08/2015 23:14:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_5_bbxjoph
7_.arc
ORA-00280: change 1032424 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
So when trying to do a cancel based recovery here we see the database report an error that its missing '/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c' archivelog file.
So i checked at os level and couldnt find the archivelog file.
Since we did shutdown immediate, we need to apply the current online redolog when it prompts for.Alternatively, we can supply the names of each of the online redo logs that we have and
Oracle will reject the one(s) that do not correspond to Sequence#6.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 YES ACTIVE 1032376 08-JAN-15 1032415 08-JAN-15
3 1 3 52428800 512 1 YES INACTIVE 1031899 08-JAN-15 1032376 08-JAN-15
2 1 5 52428800 512 1 NO CURRENT 1032415 08-JAN-15 2.8147E+14
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1033111 generated at 01/08/2015 23:17:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00280: change 1033111 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prim/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select * from soumya.xyz;
ID
----------
1
1
1
1
1
So we see we have the table as well which we created after taking the full backup.
Please share your ideas and opinions about this topic.
If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.
No comments:
Post a Comment