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>  

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.

Alertlog file :

Mon Feb 28 18:25:47 2016

ALTER DATABASE OPEN

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

How To Take Backup Database Link In Oracle

For All DBlinks backup:

-------------------------------

SQL> set pages 0

SQL> set long 90000

SQL> set trimspool on

SQL> SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;

For a specific DBlink backup:

--------------------------------------

SQL> set pages 0

SQL> set long 90000

SQL> set trimspool on

SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links

Exercise : RMAN

                RMAN Interview Questions

1. What is RMAN ?
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

Which Files must be backed up?
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)

 2.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?

In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog). 

3.  What are the Architectural components of RMAN?

1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

4.  What are Channels?

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:

Type of I/O device being read or written to, either a disk or an sbt_tape

Number of processes simultaneously accessing an I/O device

Maximum size of files created on I/O devices

Maximum rate at which database files are read

Maximum number of files open at a time

5.  Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.

6.  What does complete RMAN backup consist of ?
A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7.  What is a Backup set?
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8.  What is a Backup piece?
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:

·       A datafile cannot span backup sets

·       A datafile can span backup pieces as long as it stays within one backup set

·       Datafiles and control files can coexist in the same backup sets

·       Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece. 

9.  What are the benefits of using RMAN?

1.      Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.