How to retrieve lost control file

-

How to retrieve lost control file

ORA-00210: cannot open the specified control file

To simulate the situation of a lost control file or disk failure, I deleted a control file while the Oracle Database was running.

SQL> alter database datafile '/u02/app/oracle/oradata/mynewdb/users01.dbf' resize 512M;
Database altered. 

SQL> ! rm -f /u02/app/oracle/oradata/mynewdb/control01.ctl

Then I shutdown the database.

SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/mynewdb/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Then I start the database up.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/mynewdb/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The database does not start as it was closed only but instance may still be running as shown by the following output:

$ ps -eaf | grep pmon
oracle    1942     1  0 01:55 ?        00:00:00 ora_pmon_mynewdb
oracle    3215  3201  0 03:12 pts/5    00:00:00 grep pmon

Resolution
~~~~~~~~~~~~~

First of all get the location(s) and filename for control file.

SQL> show parameter control_file;
NAME                         TYPE   VALUE
------------------------------------ ----------- ---------------------------
control_file_record_keep_time      integer      7
control_files                      string /u02/app/oracle/oradata/mynewd
                                     b/control01.ctl, /u03/app/orac
                                     le/fast_recovery_area/mynewdb/
                                     control02.ctl

Note: You can also use init parameter file or alert log file for this purpose.

Now to solve the problem all you have to make sure is that a valid control file is placed at all the locations with their correct file name. We assume that you have multiplexed your control file (as shown above) and lost the control file from one of the location.

Now do the following steps:

1. Shutdown the database.

SQL> shutdown abort;
ORACLE instance shut down.

2. Copy the control file from one valid location to the missing location(s).

SQL> ! cp /u03/app/oracle/fast_recovery_area/mynewdb/control02.ctl /u02/app/oracle/oradata/mynewdb/control01.ctl

3. Now Start the database.

SQL> startup

ORACLE instance started.
...................
....................
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode from v$database;

NAME    OPEN_MODE
--------- --------------------
MYNEWDB   READ WRITE

Note: Your data file resize operation was successful and if you see, it do not lost in the restoration operation.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more