How to backup and restore the Oracle Control File

-

There are two approaches to take Backup of control file in Oracle:

  • Backing it up in a binary format
  • Backing it up in a human readable format
Syntax: 
alter database backup controlfile to ['filename' | trace]
This command comes in two versions. One backs up the control file in a binary format while the other backs it up in a human readable form. It is required if the database is running in archive log mode and a structural change was made to the database.

Backing it up in a binary format:

You can generate a binary image of the Control File
SQL Code: 
alter database backup controlfile
               to '/some/arbitrary/path';
alter database backup controlfile
               to '/some/arbitrary/path' reuse;

Backing it up in a human readable format:

You generate a text file script which will re-generate a Control File when run as a SQL script. The file name will be something like ‘ora_<some numbers>.trc’
SQL Code:
alter database backup controlfile to trace;
Check udump directory for text based controlfile.
Syntax:
alter database backup controlfile to trace
               as '/some/arbitrary/path';
alter database backup controlfile to trace
               as '/some/arbitrary/path' reuse;
If the human readable form is chosen, the file can be made usable if the comments at the beginning are removed and replaced with a connect / as sysdba. If the init.ora file is not at its default location, it has to be appended with a pfile=…. in the line containing a startup.

Restore the control file

The trouble starts when you attempt to restore the binary version of the Control File backup. Because it was an exact, binary copy of a Control File, its SCN number will not agree with the SCN in the headers of all the data files -basically, the Master Clock is out of whack. You therefore have to issue the following command
RECOVER DATABASE USING BACKUP CONTROLFILE;
It tells the system not to pay too much attention to the SCN of the Control File. Unfortunately, after you issue that command (and following any recovery that it might cause to take place), you must open the database with the following command:
ALTER DATABASE OPEN RESETLOGS;
You can also use RMAN script to restore and recover control file to all locations specified in the parameter file then restore the database, using that control file:
SQL Code
STARTUP NOMOUNT;
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  RESTORE CONTROLFILE;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
}

Restore control file to default location:

The default location is defined by CONTROL_FILES parameter of pfile/spfile. If you don’t specify any location while restoring your control file then the control file will be restored to the location set by CONTROL_FILES parameter.
RMAN Code: 
RMAN> SET DBID 7887865467
RMAN> RUN {
      RESTORE CONTROLFILE FROM AUTOBACKUP;
     }

Restore of the Control File from Control File Autobackup

For the use who are using  recovery catalog, you can restore your control file from an autobackup. The database must be in a NOMOUNT state. And you have to set DBID. RMAN uses the autobackup format and DBID to determine where to find for the control file autobackup.
RMAN Code:
RMAN> SET DBID 7887865467
RMAN> RUN {
        SET CONTROLFILE AUTOBACKUP FORMAT
        FOR DEVICE TYPE DISK TO 'autobackup_format';
        RESTORE CONTROLFILE FROM AUTOBACKUP;
      }

Restoring a Control File When Using a Recovery Catalog

The recovery catalog contains a complete record of the backups of Database, including backups of the control file. Therefore, It is not necessary to mention the DBID or control file autobackup format.
RMAN Code:
$rman TARGET / CATALOG cpdb/cpdb
RMAN> RESTORE CONTROLFILE;

Restore of the Control File From a Known Location

If you know the backuppiece of controlfile or any copy then simply you can use,
RMAN Code: 
RMAN> RESTORE CONTROLFILE from 'filename';

Restore of the Control File to a New Location

In prior cases RMAN restore the control file to the location specified by CONTROL_FILES parameter of the spfile or pfile.If you want to restore the control file to another location use,
RMAN Code:
RMAN> RESTORE CONTROLFILE TO '<new_location>';
It is also possible to change CONTROL_FILES parameter and then perform RESTORE CONTROLFILE to change location.

Limitations When Using a Backup Control File

After Complete the restore and recover the control file  using a backup control file, It is mandatory  run RECOVER DATABASE and perform an OPEN RESETLOGS on the database. Where SCN is change.

I have Take a backup using RMAN in following way.

Backup Control file using RMAN
 
RMAN nocatalog target /
RMAN> BACKUP CURRENT CONTROLFILE;

Scenario: I have deleted the control files now tiring to start the database; I have got the following error message successfully.

SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              67110244 bytes
Database Buffers           96468992 bytes
Redo Buffers                2945024 bytes
ORA-00205: error in identifying control file, check alert log for more info

Recover Control file using RMAN
RMAN nocatalog target /
RMAN> Restore controlfile from autobackup;
Or
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;


Scenario: incomplete database recovery

SQL> Select name from v$database;
 
NAME
---------
CJPROD
 
SQL> select status from v$instance;
 
STATUS
------------
MOUNTED
 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CJPROD\SYSTEM01.DBF'
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
 
SQL> recover database using backup controlfile until cancel
ORA-00279: change 551523 generated at 03/13/2011 00:15:03 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\CJPROD\ARCHIVELOG\2011_03_13\O1_MF_1_3_%U_.ARC
ORA-00280: change 551523 for thread 1 is in sequence #3
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.2.0\oradata\cjprod\REDO02.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
 
Database altered.
 

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

-
SQL SERVER – Event ID 107- Report Server Windows Service cannot connect to the report server database. Error with Report Server One of my clients reported an error which is the subject of the blog. In the event viewer they were seeing below error:     Event Type: Error Event Source: Report Server Windows Service (MSSQLSERVER) Event Category: Management Event ID: 107 Computer: SQLSERVER Description: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database The error is generic because it doesn’t tell the exact error which is causing a connectivity error. My immediate question was that, are there any more errors? So, I asked them to look at Reporting Service Log file. Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. The application domain WindowsService_17 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report se
Read more

SQL Server Builds Information

-
Image
Very Useful information about SQL Server Builds .  It is nicely presented in the blog . Please see  below for our reference . Microsoft SQL Server Version List What version of SQL Server do I have? This unofficial build chart lists all of the known Service Packs ( SP ), Cumulative Updates ( CU ), patches, hotfixes and other builds of MS SQL Server 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Useful articles: How to identify your SQL Server version and edition SQL Server Internal Database Versions Microsoft SQL Server Support Lifecycle Microsoft SQL Server Home Microsoft SQL Server Developer Center Microsoft TechNet: Microsoft SQL Server Microsoft Knowledge Base Sqlservr.exe versions   Quick summary:   RTM (no SP ) SP1 SP2 SP3 SP4   SQL Server 2017      codename vNext 14.0.1000.169 *new           SQL Server 2016 13.0.1601.5 13.0.4001.0 or 13.1.4001.0 13.0.5026.0 or 13.2.5026.0   S
Read more

Using DBCA silent install and disabling automatic memory management

-
Image
Avoid issue [DBT-11211] /app/oracle/product/12.2.0/dbhome_1/bin=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 20 -emConfiguration NONE [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.    CAUSE: The current total physical memory is 11GB. Solution : disable automatic memory management with in the response file and you will be fine :) .  /app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -automaticMemoryManagement FALSE -memoryPercentage 20 -emConfiguration NONE Enter SYS user password: Enter SYSTEM user password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.    CAUS
Read more