Recovering control files from corruption

Controlfile Corruption:
---------------------------------------------


Scenario 1: One of the control files is corrupted
--------------------

Starting database with one of the control file corrupted:
------------------------------------------------------------------

SQL> startup
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  2928728 bytes
Variable Size             452988840 bytes
Database Buffers          272629760 bytes
Redo Buffers                5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info


Look into the alertlog for errors. The below errors in the alertlog show the controlfile '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' is corrupted

Error in the alert log file:
--------------------------------
ALTER DATABASE   MOUNT
Sun Jan 07 12:01:38 2018
Using default pga_aggregate_limit of 2048 MB
Sun Jan 07 12:01:38 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: alter database mount...


Identify the control files:
-----------------------------

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                           ontrol01.ctl, /u01/app/oracle/
                                                           fast_recovery_area/orcl/contro
                                                           l02.ctl



 Solution: As the alert log shows that control file control02.ctl is corrupted.Copying the good control file(control01.ctl) to corrupted control file(control02.ctl) location will solve the problem and then mount and open database.

Copying control file:
-------------------------
[oracle@pavanorcl101 orcl]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl



SQL> alter database mount;

Database altered.


SQL> alter database open;

Database altered.


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



Scenario2: All controlfiles are corrupted
-------------------

Starting database with all control files corrupted:
-----------------------------------------------------------

SQL> startup
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  2928728 bytes
Variable Size             452988840 bytes
Database Buffers          272629760 bytes
Redo Buffers                5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info


 Identify the control files:
-------------------------------

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                           ontrol01.ctl, /u01/app/oracle/
                                                           fast_recovery_area/orcl/contro
                                                           l02.ctl


Error in the alert log file:
--------------------------------
ALTER DATABASE   MOUNT
Sun Jan 07 13:27:13 2018
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...


Solution: Connect to recovery manager and issue restore control file along with path where the backed up control file is located. we have given the entire path because with mounting the database RMAN doesn't know of the backup location. Then restore and reocover the database.



[oracle@pavanorcl101 2017_12_14]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 7 13:29:22 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)


Restoring control file from backup:
------------------------------------------

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_14/o1_mf_ncsnf_TAG20171214T121656_f35dlz61_.bkp';

Starting restore at 07-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 07-JAN-18



RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1



Now we can open the database

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:16
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



We just restored the control files. In order to open the database, we have to use the RESETLOGS option.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/07/2018 13:37:30
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

The above open command throws an error because the control file restored from the backup is older than the datafiles. so, we need to restore  the database


Restoring database:
-----------------------

RMAN> restore database;

Starting restore at 07-JAN-18
Starting implicit crosscheck backup at 07-JAN-18
allocated channel: ORA_DISK_1

 Recovering database:
--------------------------

RMAN> recover database;

Starting recover at 07-JAN-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 59 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_12_14/o1_mf_1_59_f35dm0t4_.arc


RMAN> alter database open resetlogs;

Statement processed



Comments

Post a Comment

Popular posts from this blog

Oracle Database Client(12.1.0.2.0) Installation for Microsoft Windows(x64)

Image
Step1) Download Oracle Client 12C from below link http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) winx64_12102_client.zip  (64-bit) (925,039,944 bytes) Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) winnt_12102_client32.zip  (32-bit) (902,922,170 bytes)  chose the one based on your laptop Step2) Extract the downloaded file Oracle 12c 64 bit client has now been extracted. Double click ‘setup.exe’. This may test your patience, let it work! (Sometimes a minute or less can seem like an eternity). Don’t touch anything! Administrator: Enables applications to connect to an Oracle database on the local system or on a remote system. It provides tools that let you administer
Read more

configuring goldengate

configuring goldengate Pre req:  Install Goldengate software on both the servers golden gate home: /u02/app/goldengate Update bash_profile on both Source and Target side [oracle@localhost ~]$ vi .bash_profile if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=test export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export GRID_HOME=/u01/app/oracle/product/12.1.0/grid export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH: export PATH=$GRID_HOME/bin:$PATH: export PATH=$ORACLE_HOME/OPatch:$PATH: PATH=$PATH:$HOME/bin export PATH export GGS_HOME=/u02/app/goldengate alias ggn='cd ${GGS_HOME}' alias ggsci='cd ${GGS_HOME}/ggsci' export LD_LIBRARY_PATH=$GGS_HOME:$ORACLE_HOME/lib:/lib:/usr/lib ".bash_profile" 25L, 684C                                     24,1          75% Source db_name: TEST target db_name: CLONE 1. check for
Read more

sample extract,pump and replicate(goldengate)

NAMING CONVENTION should be followed in any environment we are Implementing GoldenGate# E starts with Extract  P starts with Pump R starts with Replicat  Here we are taking extract: EWORK pump: PWORK replicat:RWORK ###we are replicating schema pavan(including all tables)### goldengate home:'/u02/app/goldengate' source side:  test GGSCI (localhost.localdomain) 2> start mgr Manager started. GGSCI (localhost.localdomain) 1> dblogin userid ogg_user@test password oracle; Successfully logged into database. GGSCI (localhost.localdomain as ogg_user@test) 2> add schematrandata pavan allcols 2017-10-09 14:22:20  INFO    OGG-01788  SCHEMATRANDATA has been added on schema pavan. 2017-10-09 14:22:20  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema pavan. 2017-10-09 14:22:21  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema pavan. Configuring Extract: GGSCI (local
Read more