In my previous post "database recovery using backup controlfile" I discussed about how to recover a database by losing all controlfile and restoring it from a previous cold backup. In this topic I am discussing very much the same topic ,but there is a difference indeed. Lets think about what will happen to the recovery process if there any structural change happens to the database after the last successful bakcup of the controlfile that we have ? Lets simulate the scenario and see what will happen.
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Mon Sep 12 20:23:23 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics and Real Application
Testing options
SQL> select name from
v$controlfile;
NAME
-----------------------------------------
/data1/control1.ctl
/data2/control2.ctl
SQL> create table ab
as select * from dba_tables;
Table created.
SQL> @switch
SQL> alter system
switch logfile;
System altered.
SQL>
SQL> /
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL>
SQL> exit
Disconnected from Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics
Take the cold backup of
your entire database/or just the controlfile
[oracle@mydb01]:[TTREC1]
$ cp /data1/control1.ctl /t4_nfs/TTREC/control1.ctl
[oracle@mydb01]:[TTREC1]
$
[oracle@mydb01]:[TTREC1]
$ cp /data2/control2.ctl /t4_nfs/TTREC/control2.ctl
[oracle@mydb01]:[TTREC1]
$
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Mon Sep 12 20:25:42 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147483648
bytes
Fixed
Size
3712904
bytes
Variable
Size
1996490872 bytes
Database
Buffers 134217728 bytes
Redo
Buffers
13062144 bytes
Database mounted.
Database opened.
SQL>
Lets do some activity to
make sure none of these operations are affected by losing a controlfile.
SQL> create table bc
as select * from dba_tables;
Table created.
SQL> @switch
SQL> alter system
switch logfile;
System altered.
SQL>
SQL> @switch
SQL> set echo on
SQL> alter system
switch logfile;
System altered.
SQL>
SQL> select name from
v$controlfile;
NAME
-----------------------------------------
/data1/control1.ctl
/data2/control2.ctl
SQL> select name from
v$datafile ;
NAME
----------------------------------------------------------------
/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf
/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf
/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf
/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf
/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf
/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf
/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf
/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf
8 rows selected.
Lets make a structural
change – Here I am going to create on tablespace .
SQL> create tablespace
TESTA datafile size 50M;
Tablespace created.
SQL> select name from
v$datafile ;
NAME
------------------------------------------------------------------
/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf
/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf
/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf
/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf
/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf
/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf
/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf
/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf
/data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf
9 rows selected.
SQL> @switch
SQL> set echo on
SQL> alter system
switch logfile;
System altered.
SQL>
SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> exit
Disconnected from Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics
[oracle@mydb01]:[TTREC1]
$
Lets lose your
controlfile-
[oracle@mydb01]:[TTREC1]
$ rm -f /data1/control1.ctl /data2/control2.ctl
[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0
Production on Mon Sep 12 20:35:53 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup;
ORACLE instance started.
Total System Global Area
2147483648 bytes
Fixed
Size
3712904 bytes
Variable
Size
1996490872 bytes
Database
Buffers 134217728 bytes
Redo
Buffers
13062144 bytes
ORA-00205: error in
identifying control file, check alert log for more info
SQL> shut abort ;
ORACLE instance shut
down.
SQL>
SQL> exit
Disconnected from Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics
Now copy back your
controlfile to the original location that you took prior to adding new
tablespace to the database.
[oracle@mydb01]:[TTREC1]
$ cp /t4_nfs/TTREC/control1.ctl /data1/control1.ctl
[oracle@mydb01]:[TTREC1]
$
[oracle@mydb01]:[TTREC1]
$ cp /t4_nfs/TTREC/control2.ctl /data2/control2.ctl
[oracle@mydb01]:[TTREC1]
$
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Mon Sep 12 20:36:43 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup;
ORACLE instance started.
Total System Global Area
2147483648 bytes
Fixed
Size
3712904 bytes
Variable
Size
1996490872 bytes
Database
Buffers 134217728 bytes
Redo
Buffers
13062144 bytes
Database mounted.
ORA-01122: database file
1 failed verification check
ORA-01110: data file 1:
'/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf'
ORA-01207: file is more
recent than control file - old control file
SQL> exit
Disconnected from Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics
Lets check the archive log created during the course of operation.
Lets check the archive log created during the course of operation.
[oracle@mydb01]:[TTREC1]
$ ls -rlt /data2/TTREC/a
archivelog/ autobackup/
[oracle@mydb01]:[TTREC1]
$ ls -rlt /data2/TTREC/archivelog/2016_09_12/
-rw-r----- 1 oracle
asmadmin 246272 Sep 12 20:21 o1_mf_1_6_cxf0fk47_.arc
-rw-r----- 1 oracle
asmadmin 29696 Sep 12 20:21 o1_mf_1_5_cxf0fk3r_.arc
-rw-r----- 1 oracle
asmadmin 1024 Sep 12 20:21 o1_mf_1_4_cxf0fk3b_.arc
-rw-r----- 1 oracle
asmadmin 1634816 Sep 12 20:23 o1_mf_1_1_cxf0klpo_.arc
-rw-r----- 1 oracle
asmadmin 1024 Sep 12 20:23 o1_mf_1_2_cxf0kpkm_.arc
-rw-r----- 1 oracle
asmadmin 1661952 Sep 12 20:26 o1_mf_1_3_cxf0prkb_.arc
-rw-r----- 1 oracle
asmadmin 1024 Sep 12 20:26 o1_mf_1_4_cxf0pvdy_.arc
-rw-r----- 1 oracle
asmadmin 37888 Sep 12 20:27 o1_mf_1_5_cxf0s1o5_.arc
[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba
SQL*Plus: Release
12.1.0.2.0 Production on Mon Sep 12 20:37:51 2016
Copyright (c) 1982, 2014,
Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP, Advanced Analytics
SQL> @redo
SQL> set lines 222
SQL> col status for
a10
SQL> col member format
a45
SQL> select member,
a.group#, a.status, b.status, a.archived from v$log a, v$logfile b
where a.group# = b.group# order by a.group#, member;
MEMBER
GROUP#
STATUS STATUS ARC
---------------------------------------------
---------- ---------- ---------- ---
/data1/TTREC/onlinelog/o1_mf_1_cwzoob3m_.log
1
INACTIVE
YES
/data2/TTREC/onlinelog/o1_mf_1_cwzoobdr_.log
1
INACTIVE
YES
/data1/TTREC/onlinelog/o1_mf_2_cwzoobs8_.log
2
INACTIVE
YES
/data2/TTREC/onlinelog/o1_mf_2_cwzooc2y_.log
2
INACTIVE
YES
/data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log
3
CURRENT
NO
/data2/TTREC/onlinelog/o1_mf_3_cwzoocvl_.log
3
CURRENT
NO
6 rows selected.
Recover the database using backup controlfile.
SQL> recover database
using backup controlfile;
ORA-00279: change
133235298 generated at 09/12/2016 20:23:50 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_3_cxf0prkb_.arc
ORA-00280: change
133235298 for thread 1 is in sequence #3
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_3_cxf0prkb_.arc
ORA-00279: change
133236031 generated at 09/12/2016 20:26:32 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_4_cxf0pvdy_.arc
ORA-00280: change
133236031 for thread 1 is in sequence #4
ORA-00278: log file
'/data2/TTREC/archivelog/2016_09_12/o1_mf_1_3_cxf0prkb_.arc' no longer needed
for this recovery
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_4_cxf0pvdy_.arc
ORA-00279: change
133236034 generated at 09/12/2016 20:26:35 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxf0s1o5_.arc
ORA-00280: change
133236034 for thread 1 is in sequence #5
ORA-00278: log file
'/data2/TTREC/archivelog/2016_09_12/o1_mf_1_4_cxf0pvdy_.arc' no longer needed
for this recovery
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxf0s1o5_.arc
ORA-00283: recovery
session canceled due to errors
ORA-01244: unnamed
datafile(s) added to control file by media recovery
ORA-01110: data file 9:
'/data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf'
ORA-01112: media recovery
not started
Use ALTER DATABASE CREATE DATAFILE to "add" the new datafile to the controlfile.
SQL> alter database create datafile 9 as '/data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf';
SQL> alter database create datafile 9 as '/data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf';
alter database create
datafile 9 as '/data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add
file /data1/TTREC/datafile/o1_mf_testa_cxf0rqq8_.dbf. File has an Oracle
Managed Files file name.
As my database configured through OMF, use following syntax to create new datafile.
SQL> alter database
create datafile 9 as new;
Database altered.
SQL> recover database
using backup controlfile;
ORA-00279: change
133236078 generated at 09/12/2016 20:27:35 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxf0s1o5_.arc
ORA-00280: change
133236078 for thread 1 is in sequence #5
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxf0s1o5_.arc
ORA-00279: change
133236097 generated at 09/12/2016 20:27:45 needed for thread 1
ORA-00289: suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_6_%u_.arc
ORA-00280: change
133236097 for thread 1 is in sequence #6
ORA-00278: log file
'/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxf0s1o5_.arc' no longer needed
for this recovery
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log
Log applied.
Media recovery complete.
Here I provided my current redo log file.
Here I provided my current redo log file.
SQL> alter database
open resetlogs;
Database altered.
SQL> select count(1)
from ab;
COUNT(1)
----------
2507
SQL> select count(1)
from bc;
COUNT(1)
----------
2508
SQL>
Note here we had
two issues :-
The restored
controlfile doesn't know the datafile of the new Tablespace (and doesn't know
the Tablespace). The controlfile maintains a map of the physical structure of
the database. You have to update this with an ALTER DATABASE CREATE DATAFILE ..
in the same manner as you would have to do if this were a Standby Database.
Since the redo log file that contains the last transactions (including the CREATE TABLESPACE) hasn't been archived, it is not "visible" as an archivelog to apply. You must apply the Redo Log itself.
Since the redo log file that contains the last transactions (including the CREATE TABLESPACE) hasn't been archived, it is not "visible" as an archivelog to apply. You must apply the Redo Log itself.
