Recovery of ControlFiles to Another Location, without AutoBackup available

Recovery of ControlFiles to Another Location, without AutoBackup available

Today , I will show you a scenario, where I lost all my current Controlfile and I don’t have any AutoBackup of Current ControlFile. And how I rescue from that situation.

Actually my MountPoint was Gone due to some issues with System Admin. Hence I Lost the Directory too.

So, I will Restore to Another Location

Lets see, What are my ControlFile Locations are . . using Below command

 SQL> show parameter control_file  
 NAME                         TYPE       VALUE  
 ---------------------------------------      -----------     ------------------------------  
 control_file_record_keep_time     integer      7  
 control_files                    string       /u01/app/oracle/oradata/orcl/c  
                                           ontrol01.ctl, /u01/app/oracle/  
                                           flash_recovery_area/orcl/contr  
                                           ol02.ctl  
 SQL>  

OK,  so now Lets Simulate this Scenario.. as Below

I’m Removing my Current Controlfiles. (Assuming that – I lost my Both ControlFiles )

 [oracle@system1 ~]$ rm -rf /u01/app/oracle/oradata/orcl/control01.ctl  
 [oracle@system1 ~]$ cd /u01/app/oracle/oradata/orcl/  
 [oracle@system1 orcl]$ ls control  
 ls: cannot access control: No such file or directory  
 [oracle@system1 ~]$ rm -rf /u01/app/oracle/flash_recovery_area/orcl/control02.ctl  

Lets open RMAN prompt & Backup the ControlFile from Memory . . using Below Command

 SQL> ! rman target /  
 RMAN> backup current controlfile to destination '/u01';  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: starting full datafile backup set  
 channel ORA_DISK_1: specifying datafile(s) in backup set  
 including current control file in backup set  
 channel ORA_DISK_1: starting piece 1 at 09-MAY-16  
 channel ORA_DISK_1: finished piece 1 at 09-MAY-16  
 piece handle=/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp tag=TAG20160509T162824 comment=NONE  
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03  
 Finished backup at 09-MAY-16  
 RMAN> exit  
 Recovery Manager complete.  

Now , when we try to shutdown immediate / or normal, we see below error.

But don’t worry. We will use Shutdown Abort.

 SQL> shut immediate  
 Database closed.  
 ORA-00210: cannot open the specified control file  
 ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'  
 ORA-27041: unable to open file  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 SQL> shut abort  
 ORACLE instance shut down.  

Again , Open your database in Nomount stage to Restore the Control File.

Here , I’m going to Restore to Another location (Other than Existing Location)

So, we need to change the parameter ‘control_files’.

 SQL> startup nomount  
 ORACLE instance started.  
 Total System Global Area 422670336 bytes  
 Fixed Size            1336960 bytes  
 Variable Size        109054336 bytes  
 Database Buffers    306184192 bytes  
 Redo Buffers         6094848 bytes  
 SQL> !rman target /  
 Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:30:05 2016  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.  
 connected to target database: ORCL (not mounted)  
 RMAN> restore controlfile to '/u01/app/oracle/oradata/orcl/control001.' from '/u01/ORCL/backupset/2016_05_09/o1_mf_ncnnf_TAG20160509T162824_cm0vbkv4_.bkp' ;  
 Starting restore at 09-MAY-16  
 using channel ORA_DISK_1  
 channel ORA_DISK_1: restoring control file  
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01  
 Finished restore at 09-MAY-16  
 RMAN> exit  
 SQL> alter system set control_files=/u01/app/oracle/oradata/orcl/control001. Scope=spfile;  
 SQL> startup mount;  
 ORACLE instance started.  
 Total System Global Area 422670336 bytes  
 Fixed Size            1336960 bytes  
 Variable Size        100665728 bytes  
 Database Buffers    314572800 bytes  
 Redo Buffers         6094848 bytes  
 Database mounted.  
 SQL>  

Now , when I try to Recover the Database , I Get an Error .

 SQL> recover database ;  
 ORA-00283: recovery session canceled due to errors  
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done  
 So, Now , I tried Another command, and Again Facing an Error  
 SQL> recover database using backup controlfile;  
 ORA-00279: change 792051 generated at 05/09/2016 16:23:23 needed for thread 1  
 ORA-00289: suggestion :  
 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.arc  
 ORA-00280: change 792051 for thread 1 is in sequence #4  
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}  
 AUTO  
 ORA-00308: cannot open archived log  
 '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar  
 c'  
 ORA-27037: unable to obtain file status  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 ORA-00308: cannot open archived log  
 '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_05_09/o1_mf_1_4_%u_.ar  
 c'  
 ORA-27037: unable to obtain file status  
 Linux Error: 2: No such file or directory  
 Additional information: 3  
 SQL> alter database open resetlogs ;  
 alter database open resetlogs  
 *  
 ERROR at line 1:  
 ORA-01113: file 1 needs media recovery  
 ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'  

So Now , Simply. . . what  I did is - - - > open RMAN - - > and Recover Database,

 SQL> !rman target /  
 Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 9 16:40:04 2016  
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.  
 connected to target database: ORCL (DBID=1433520325, not open)  
 RMAN> recover database ;  
 Starting recover at 09-MAY-16  
 Starting implicit crosscheck backup at 09-MAY-16  
 using target database control file instead of recovery catalog  
 allocated channel: ORA_DISK_1  
 channel ORA_DISK_1: SID=17 device type=DISK  
 Crosschecked 1 objects  
 Finished implicit crosscheck backup at 09-MAY-16  
 Starting implicit crosscheck copy at 09-MAY-16  
 using channel ORA_DISK_1  
 Finished implicit crosscheck copy at 09-MAY-16  
 searching for all files in the recovery area  
 cataloging files...  
 no files cataloged  
 using channel ORA_DISK_1  
 starting media recovery  
 archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/oradata/orcl/redo01.log  
 archived log file name=/u01/app/oracle/oradata/orcl/redo01.log thread=1 sequence=4  
 media recovery complete, elapsed time: 00:00:00  
 Finished recover at 09-MAY-16  
 RMAN> exit  
 Recovery Manager complete.  
 SQL> alter database open resetlogs;  
 Database altered.  
 SQL>  

Wednesday, 27 April 2016

Oracle Block Change Tracking

RMAN block change tracking

With Oracle 10gr1 Enterprise Edition Oracle introduce Block change tracking feature which is used to Speed up RMAN Incremental Backup.

What is BLOCK CHANGE TRACKING?

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Source: Oracle documentation

1) To  Enable Block Change tracking and set location

Before enabling we must to set/specify block change tracking location.There is two way to specify block change tracking location

1. using DB_CREATE_FILE_DEST parameter which use OMF file name.

SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;

SQL> alter database enable block change tracking;

OR

2. Manually specify location for the block change tracking

SQL>alter database enable block change tracking using file 'location';

2) To Disable block change tracking

SQL> alter database disable block change tracking;

NOTE: BCT files deleted automatically by oracle when BCT is disabled.

3) To Reset the location of Block change tracking file

There are two options for this

1. shutdown the database and set the new location

SQL> shutdown database

--Now Move BCT file to new location using OS command ‘mv’

SQL> startup mount

SQL> alter database rename file 'old_location' TO 'new_location';

SQL> alter database open;

OR

Follow these step –

Disable the block change tracking , then set the new location, then enable BCT

SQL> alter database DISABLE block change tracking;

SQL> alter database enable block change tracking using FILE 'NEW_LOCATION';

After these,  RMAN use new location for the BCT.

4) To Check the information about block change tracking STATUS - enable or disabled

Check v$BLOCK_CHANGE_TRACKING view

5). Does BCT file is important for restore and recovery of database.. ?

Answer: No, it is doesn't require for Database Recovery of database

6). What happen if BCT file is lost or corrupted..?

Answer: That is very interesting case here, suppose oracle found if BCT file is corrupted or missing then oracle will automatically recreate new BCT file.

Let see some practical view...

Assume that, BCT file lost or missing :

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host rm /u01/block/bct.dbf

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

Now check the alertlog file for more information about how oracle create new BCT file when missing or lost.