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.
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.
No comments:
Post a Comment