In this article, we will see how to recover
the database when one of the multiplexed controlfile is lost.
Controlfile is the critical component of
Oracle database and this stores the names and location of the data files, log
files, database name, block size, RMAN catalog information and character set.
1.
List the controlfiles in the
database.
SQL> select name from
v$controlfile;
NAME
-----------------------------------------------------------------
/data1/noasmdb/redofiles/NOASMDB/control01.ctl
/data1/noasmdb/control02.ctl
/data1/noasmdb/redofiles/NOASMDB/controlfile/control03.ctl
2.
Next, we will see the status of
the datafile in v$datafile and v$datafile_header views.
SQL> select file#,name,STATUS,TS#,block_size
from v$datafile;
FILE# NAME
STATUS TS# BLOCK_SIZE
----------
----------------------------------------------------------------- -------
---------- ----------
1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192
2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192
3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7 8192
4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192
5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192
6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192
7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192
7 rows selected.
SQL> select
file#,tablespace_name,name,status from v$datafile_header;
FILE# TABLESPACE_NAME
NAME
STATUS
----------
------------------------------ -----------------------------------------------------------------
-------
1 SYSTEM
/data1/noasmdb/datafile/system01.dbf ONLINE
2 SYSAUX
/data1/noasmdb/datafile/sysaux01.dbf ONLINE
3 TEST_BKP
/data1/noasmdb/datafile/testbkp01.dbf ONLINE
4 USERS
/data1/noasmdb/datafile/users01.dbf ONLINE
5 UNDOTBS
/data1/noasmdb/datafile/undotbs01.dbf ONLINE
6 NEWTEST
/data1/noasmdb/datafile/newtest01.dbf ONLINE
7 NOVALIDTBS
/data1/noasmdb/datafile/novalidtbs01.dbf ONLINE
7 rows selected.
3.
Next, we will remove one of the
control file “control02.ct1” .
[oracle@vm1 noasmdb]$ ls -ltrh
total 10M
drwxr-xr-x 2 oracle oinstall 4.0K Feb 11
14:43 datafile
drwxr-xr-x 2 oracle oinstall 4.0K Feb 14
12:32 archivelog
drwxr-xr-x 3 oracle oinstall 4.0K Feb 14
12:49 redofiles
-rw-r----- 1 oracle oinstall 9.9M Feb 14
14:01 control02.ctl
[oracle@vm1 noasmdb]$ rm control02.ctl
[oracle@vm1 noasmdb]$
4.
Lets try to query v$datafile,
v$datafile_header views now. I am able
to query the views . This mean that the database is operating fine. We will be
getting alert log errors also.
SQL> select
file#,name,STATUS,TS#,block_size from v$datafile;
FILE# NAME
STATUS TS# BLOCK_SIZE
----------
----------------------------------------------------------------- -------
---------- ----------
1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192
2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192
3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7
8192
4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192
5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192
6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192
7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192
7 rows selected.
Fri Feb 14 14:04:40 2014
Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_m000_10799.trc:
ORA-00210: cannot open the specified
control file
ORA-00202: control file:
'/data1/noasmdb/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
This concludes that this controlfile is
not a subset and it is not the main file used by the datafiles.
5.
Now, lets remove another
control file and see.
[oracle@vm1
NOASMDB]$ ls -ltrh
total 10M
drwxr-x--- 2
oracle asmadmin 4.0K Feb 8 20:58
onlinelog
drwxr-x--- 2
oracle asmadmin 4.0K Feb 8 21:24
datafile
drwxr-x--- 2
oracle asmadmin 4.0K Feb 14 12:48 controlfile
-rw-r----- 1
oracle oinstall 9.9M Feb 14 14:07 control01.ctl
[oracle@vm1
NOASMDB]$ rm control01.ctl
6.
Now, when we try to login using
RMAN , we get following error. But the
database is not crashed as we were able to query.
[oracle@vm1
backup]$ rman target sys/sys catalog=rman_cat/rman_cat@testdb
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Feb 14 14:11:46 2014
Copyright (c)
1982, 2011, Oracle and/or its affiliates.
All rights reserved.
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-06003:
ORACLE error from target database:
ORA-00210:
cannot open the specified control file
ORA-00202:
control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041:
unable to open file
Linux-x86_64
Error: 2: No such file or directory
Additional
information: 3
Also, when you
try for a fresh SQLPLUS session to connect, we get the following error.
SQL> select
file#,tablespace_name,name,status from v$datafile_header;
select
file#,tablespace_name,name,status from v$datafile_header
*
ERROR at line
1:
ORA-00210:
cannot open the specified control file
ORA-00202:
control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041:
unable to open file
Linux-x86_64
Error: 2: No such file or directory
Additional
information: 3
Now, we have simulated in losing of one
of the controlfile.
7.
Next, we have to see the
parameter of control file to check the locations of the controlfiles.
SQL> show
parameter control;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /data1/noasmdb/redofiles/NOASM
DB/control01.ctl, /data1/noasm
db/control02.ctl, /data1/noasm
db/redofiles/NOASMDB/controlfi
le/control03.ctl
8.
The only option is to copy the
unaffected controlfile “control03.ctl”
to all these locations and rename it. To
do this, we need to shutdown the database. Even the “shutdown immediate” will
not happen.
SQL> shut
immediate;
ORA-00210:
cannot open the specified control file
ORA-00202:
control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'
ORA-27041:
unable to open file
Linux-x86_64
Error: 2: No such file or directory
Additional
information: 3
SQL> shut
abort;
ORACLE instance
shut down.
[oracle@vm1
controlfile]$ cp control03.ctl /data1/noasmdb/redofiles/NOASMDB/control03.ctl
[oracle@vm1
controlfile]$ cp control03.ctl /data1/noasmdb/control03.ctl
[oracle@vm1
controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/
[oracle@vm1
NOASMDB]$ ls
[oracle@vm1
noasmdb]$ pwd
/data1/noasmdb
[oracle@vm1
noasmdb]$ mv control03.ctl control02.ctl
[oracle@vm1
controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/
[oracle@vm1
NOASMDB]$ mv control03.ctl control01.ctl
9.
Let’s start the database now
and see what happens.
SQL> startup
ORACLE instance started.
Total System Global Area 263049216 bytes
Fixed Size 2227456 bytes
Variable Size 188744448 bytes
Database Buffers 67108864 bytes
Redo Buffers 4968448 bytes
Database mounted.
Database opened.
Now, we are able to query the database.
SQL> select
file#,tablespace_name,name,status from v$datafile_header;
FILE# TABLESPACE_NAME
NAME
STATUS
----------
------------------------------
----------------------------------------------------------------- -------
1 SYSTEM
/data1/noasmdb/datafile/system01.dbf ONLINE
2 SYSAUX
/data1/noasmdb/datafile/sysaux01.dbf ONLINE
3 TEST_BKP /data1/noasmdb/datafile/testbkp01.dbf ONLINE
4 USERS
/data1/noasmdb/datafile/users01.dbf ONLINE
5 UNDOTBS
/data1/noasmdb/datafile/undotbs01.dbf ONLINE
6 NEWTEST
/data1/noasmdb/datafile/newtest01.dbf ONLINE
7 NOVALIDTBS
/data1/noasmdb/datafile/novalidtbs01.dbf ONLINE
7 rows selected.
SQL> select
file#,name,STATUS,TS#,block_size from v$datafile;
FILE# NAME
STATUS TS# BLOCK_SIZE
---------- -----------------------------------------------------------------
------- ---------- ----------
1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192
2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192
3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7 8192
4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192
5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192
6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192
7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192
7 rows selected.
[oracle@vm1 backup]$ rman target sys/sys
catalog=rman_cat/rman_cat@testdb
Recovery Manager: Release 11.2.0.3.0 -
Production on Fri Feb 14 14:27:16 2014
Copyright (c) 1982, 2011, Oracle and/or
its affiliates. All rights reserved.
connected to target database: NOASMDB
(DBID=1704132256)
connected to recovery catalog database
RMAN>
Conclusion
If one of the multiplexed control file
is lost, we need to perform following.
1.
Shutdown the database
2.
Copy the unaffected files.
3.
Start the database.
No comments:
Post a Comment