Flashback table feature on Oracle 11g

FLASHBACK TABLE statement is used to restore an earlier state of a table in the  event of human or application error.Though It entirely depends  on the amount of undo data that is
present in the system.Also we cant flashback a table to earlier stage in case of any ddl operation that changes the table structure.flashback on is not required in order to do
the flashback table.
You cannot 'flashback table to before drop' a table which has been created in the SYSTEM tablespace.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create user soumya identified by soumya default tablespace users;

User created.
[oracle@server1 ~]$ conn soumya/soumya
SQL> create table test ( id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test;

        ID
----------
         1
         1
         1

SQL> drop table test;

Table dropped.


SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNA/E4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:23:47

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

        ID
----------
         1
         1
         1

Now lets try to flashback a table which resides in system tablespace.
SQL> show user
USER is "SYS"
SQL> create table flash (id number);    

Table created.

SQL> insert into flash values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_recyclebin;

no rows selected

SQL> show recyclebin;
SQL>

SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

So, if a table resides in system tablesapce and if its dropped it doesnt stay in recylebin, rather its being dropped permamnently from the database.

To query a dropped table:-
SQL> drop table test;

Table dropped.
SQL>  show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:37:02
While querying the recycle bin, make sure the system generated table name is enclosed in double quotes, else it will throw error.


SQL> select * from BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 ;

SQL> select * from "BIN$Ig9YoNBCE4/gUKjAZgINCg==$0" ;
        ID
----------
         1
         1
         1


Now lets try to insert some data inside the dropped table which is inside recyclebin.
SQL> insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2);
insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


So we can not perform DDL/DML over objects in Recycle Bin.

Flashback a table in the past to a specific point in time:-
SQL> set time on
16:52:51 SQL>
16:52:52 SQL>  alter table test enable row movement ;

Table altered.

16:53:21 SQL> select * from test;

        ID
----------
         1
         1
         1
         2

16:53:37 SQL>
16:53:44 SQL> update test set id=100 where id=1 ;

3 rows updated.

16:54:00 SQL> commit;

Commit complete.

16:54:04 SQL> select * from test;

        ID
----------
       100
       100
       100
         2

Now lets flashback the table
17:25:05 SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2015-10-14 16:54:01' ,'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

17:25:10 SQL>  select * from test;

        ID
----------
         1
         1
         1
         2

17:25:17 SQL>



To rename an object while flashing back from recyclebin:-
17:37:39 SQL> create table test11(id number);

Table created.

17:37:58 SQL>  insert into test11 values (1);

1 row created.

17:38:05 SQL> commit;

Commit complete.

17:38:09 SQL> drop table test11;

Table dropped.

17:39:21 SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST11           BIN$Ig9YoNBIE4/gUKjAZgINCg==$0 TABLE        2015-10-14:17:39:21

17:39:57 SQL> flashback table "BIN$Ig9YoNBIE4/gUKjAZgINCg==$0" to before drop rename to test12 ;
Flashback complete.

17:40:11 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST12                       TABLE

17:50:18 SQL> select * from test12;

        ID
----------
         1




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.

ORA-01466: unable to read data - table definition has changed

ORA-01466: unable to read data - table definition has changed

01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
//         w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute

While selecing a table for a specific point of time i faced the error.

15:23:07 SQL> SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS')
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


Reason:- There could be few reasons behind it.
1. DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for
the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

2.You need to have the time of your client (where you run sqlplus) set to a later (or same) value than the time of your database server.Else such error could generate.
3. This could be caused by a long running snapshot. Try committing or rolling-back all outstanding transactions and try again.
4.It also could happen if the table is newly created .


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.

RMAN throwing error no AUTOBACKUP found of controlfile

RMAN throwing error no AUTOBACKUP found of controlfile

Error:- RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Reason:-
Basically we get to see 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.


Database Version:- Oracle 11g R2
Database SID:- prim

So, here i'm trying to produce a test case to demonstrate the error.

[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

Now lets drop the controlfile from system and recover it from the backup.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> select dbid from v$database;

      DBID
----------
4130009889

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


So to overcome the above issue we 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

So using the following way we can restore the controlfile from autobackup.



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.

RECOVER DATABASE USING BACKUP CONTROLFILE fails due to no archivelog present

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.