# su - <crs-user> -c "<CRS_HOME>/bin/crsctl check boot"
# echo $?
i.e.
# su - crsuser -c "/ocw/crs/bin/crsctl check boot"
# echo $?
0
# echo $?
i.e.
# su - crsuser -c "/ocw/crs/bin/crsctl check boot"
# echo $?
0
Friday, 27 May 2016
The Block Change Tracking File
When using Oracle block change tracking we see this procedure. As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.
When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.
The block change tracking file is maintained in the directory defined by the db_create_file_dest parameter, if it is configured. You can also define the location of the block change tracking file via the using file clause of the alter database enable block change tracking command. You can rename the block change tracking file with the alter database rename file command, just as you would with any normal database file. Here is an example of turning on block change tracking, using the using file clause to define where the block change tracking file should be:
Alter database enable block change tacking
using file '/u01/oracle/RMAN/blocktrack/my_db_tracking.chg';
The block change tracking file size depends on svereal considerations, including the number of enabled redo threads, the size of the database, and the number of RMAN backups for which change data needs to be stored. The formula looks like this:
R=((T * 2)+ B) * (S/250000)
where:
R = The size of the block change tracking file, in bytes
T = The number of enabled redo threads
S = The size of the database, in bytes
B = The number of incremental backups for which RMAN needs to store change tracking data
- To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.
- For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.
Tuesday, 17 May 2016
Restoring previous version of stats
Scenario : Performance of certain queries has deteriorated suddenly after collecting fresh statistics . You want to revert the stats to previous values .
Solution : Use the dbms_stats.restore_stats procedure to revert to an older set of optimizer statistics .
1. Check the retention of stats for your database
SQL > select dbms_stats.get_Stats_history_retention from dual
GET_STATS_HISTORY_RETENTION
--------------------------------------------------
31
2. Check how far you can go to restore the stats :
SQL> select dbms_stats.get_stats_history_availability from dual ;
GET_STATS_HISTORY_AVAILABLITY
--------------------------------------------------
17-APR-2016 03:05:26.7180000000 AM
3. exec dbms_stats.restore_schema_stats(ownername=>'SUMIT' , as_of_timestamp = > '15-May-2016 01.30.30.211212121 PM' , no_invalidate=> false ) ;
Solution : Use the dbms_stats.restore_stats procedure to revert to an older set of optimizer statistics .
1. Check the retention of stats for your database
SQL > select dbms_stats.get_Stats_history_retention from dual
GET_STATS_HISTORY_RETENTION
--------------------------------------------------
31
2. Check how far you can go to restore the stats :
SQL> select dbms_stats.get_stats_history_availability from dual ;
GET_STATS_HISTORY_AVAILABLITY
--------------------------------------------------
17-APR-2016 03:05:26.7180000000 AM
3. exec dbms_stats.restore_schema_stats(ownername=>'SUMIT' , as_of_timestamp = > '15-May-2016 01.30.30.211212121 PM' , no_invalidate=> false ) ;
Saturday, 14 May 2016
How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database?
Estimate The Size Of Tables
The CREATE_TABLE_COST procedure of the DBMS_SPACE package helps to estimate the size of the table segment ,
This procedure is used in capacity planning to determine the size of the table given various attributes , The CREATE_TABLE_COST procedure has two versions :
- The first version takes the average row size of the table as argument and outputs the table size.
- The second version takes the column information of the table as argument and outputs the table size.
Both Versions also Requires the following input :
- the expected number of rows
- pct_free setting for the table
- the tablespace name where the table would be created.
Version 1
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
The output of the procedure contains used_bytes and alloc_bytes :
- The used_bytes : represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
- The alloc_bytes : represent the size of the table segment when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.
Example for using Version 1:
This methode requires creating the table first with some sample data to calculate the average row size of the table ,
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';
AVG_ROW_LEN
-----------
68
Drop table test;
set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
BEGIN
DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 8036352
Allocated Bytes = 8388608
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';
AVG_ROW_LEN
-----------
68
Drop table test;
set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
BEGIN
DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 8036352
Allocated Bytes = 8388608
which is around 8 MB , now lets create and populate the actual table.
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
which is around 9 MB
Example for using Version 2:
This example estimate a Table size with one column NUMBER (10) , two columns VARCHAR2 (30) and one column date , with pct_free=10 and row_count=100000 :
set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 9314304
Allocated Bytes = 9437184
DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 9314304
Allocated Bytes = 9437184
which is around 9 MB , now lets create and populate the actual table.
Note : we changed VARCHAR2 to CHAR to get the maximum possible estimation .
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
which is around 9 MB
You may notice that version 2 of The CREATE_TABLE_COST procedure is more accurate than version 1 , which is expected because version 2 estimates the size based on the table structure which includes the column data types and length , plus the storage attributes .
Estimate The Size Of Indexes
The CREATE_INDEX_COST Procedure of the DBMS_SPACE package helps to estimate the size of creating an index on an existing table.
The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.
Usage Notes :
- The table on which the index is created must already exist.
- The computation of the index size depends on statistics gathered on the segment.
- It is imperative that the table must have been analyzed recently.
- In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.
The output of the procedure contains used_bytes and alloc_bytes :
- The used_bytes : how much space is for the index data
- The alloc_bytes : how much space is allocated within the tablespace for the index segment.
Estimate Index Size example :
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
set serveroutput on
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index test_indx on test (a,b) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
Used Bytes = 3800000
Allocated Bytes = 6291456
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
set serveroutput on
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index test_indx on test (a,b) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
Used Bytes = 3800000
Allocated Bytes = 6291456
which is around 6 MB , now lets create the actual index.
create index test_indx on test (a,b) tablespace users
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';
BYTES SEGMENT_TYPE
---------- ------------------
6291456 INDEX
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';
BYTES SEGMENT_TYPE
---------- ------------------
6291456 INDEX
which is around 6 MB
Reference oracle support (Doc ID 1585326.1)
Saturday, 26 March 2016
How to find OMS repository database information
1. Check in the file emgc.properties for the parameter EM_REPOS_CONNECTDESCRIPTOR .
EM_REPOS_CONNECTDESCRIPTOR=(DESCRIPTION\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=hostname-scan.abc.com)(PORT\=1521))(CONNECT_DATA\=(SERVER\=DEDICATED)(SERVICE_NAME\=dbname_service)))
2. emctl config oms -list_repos_details
C:\oracle\product\mw\oms\BIN>emctl config oms -list_repos_details
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Repository Connect Descriptor : (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname-scan.abc.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbname_service)))
Repository User : sysman
Tuesday, 22 March 2016
Why odd number of voting disk
Simple Majority Rule, According to Oracle – “An absolute majority of voting disks configured (more than half) must be available and responsive at all times for Oracle Clusterware to operate.” Which means to survive from loss of ‘N’ voting disks, you must configure atleast ‘2N+1′ voting disks.
OR we can say a node must be able to see trun(n/2 + 1) voting disks to survive as a part of cluster .
- When you have 1 voting disk and it goes bad, the cluster stops functioning.
- When you have 2 and 1 goes bad, as per simple majority rule cluster stop functioning
- When you have 3 and 1 goes bad, the cluster runs fine .
- When you have 3 and 2 go bad, the cluster stops .
- When you have 4 and 1 goes bad, cluster run fine .
- When you have 4 and 2 go bad, the same, because the nodes can only access half, not > half.
- So you see 4 voting disks have the same fault tolerance as 3, but you waste 1 disk, without gaining anything.
- The recommendation for odd number of voting disks helps save a little on hardware requirement.
Wednesday, 9 March 2016
Redo Failure : Inactive Redo failed
Alert log :
Tue Mar 08 07:50:39 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8471.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 07:50:39 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8471.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxht4z2_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxht4vz_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Check the status of members if they are INACTIVE :
MEMBER GROUP# STATUS ARCHIVED
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log 1 INACTIVE NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log 1 INACTIVE NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxht4z2_.log 2 INACTIVE NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxht4vz_.log 2 INACTIVE NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
As the status in INACTIVE, log group 1 and 2 is no longer needed for crash recovery .Therefore, you can use the clear logfile command to re-create all members of a log group. The following example re-creates all log members of group 1 and 2 :
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log'
ORA-00312: online log 1 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log'
If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2 ;
Database altered.
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
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxm3zbt_.log 1 UNUSED YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxm3z7v_.log 1 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 UNUSED YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
6 rows selected.
SQL> Alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxm3zbt_.log 1 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxm3z7v_.log 1 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
6 rows selected.
Tue Mar 08 07:50:39 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8471.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 07:50:39 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8471.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxht4z2_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxht4vz_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Check the status of members if they are INACTIVE :
MEMBER GROUP# STATUS ARCHIVED
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log 1 INACTIVE NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log 1 INACTIVE NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxht4z2_.log 2 INACTIVE NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxht4vz_.log 2 INACTIVE NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
As the status in INACTIVE, log group 1 and 2 is no longer needed for crash recovery .Therefore, you can use the clear logfile command to re-create all members of a log group. The following example re-creates all log members of group 1 and 2 :
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxht2q1_.log'
ORA-00312: online log 1 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxht2w0_.log'
If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database clear unarchived logfile group 2 ;
Database altered.
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
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxm3zbt_.log 1 UNUSED YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxm3z7v_.log 1 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 UNUSED YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
6 rows selected.
SQL> Alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
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
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxm3zbt_.log 1 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxm3z7v_.log 1 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
6 rows selected.
Redo Failure : Active Redo lost
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 771754072 bytes
Database Buffers 352321536 bytes
Redo Buffers 9383936 bytes
Database mounted.
SQL> Alter database open ;
Alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
MEMBER GROUP# STATUS ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxmxybl_.log 1 INACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxmxy5d_.log 1 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log 4 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log 4 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log 5 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log 5 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log 6 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log 6 ACTIVE YES
Perform the following steps when restoring an active online redo log group:
• Verify the damage to the members.
• Verify that the status is ACTIVE.
• Attempt to issue a checkpoint. (alter system checkpoint;) If the checkpoint completes successfully, then the active log group should be marked as INACTIVE. A successful checkpoint ensures that all modified database buffers have been written to disk, and at that point, only transactions contained in the CURRENT online redo log will be required for crash recovery.,so you can clear the log group.
• If the log group that was cleared was unarchived, back up your database immediately.
• If the checkpoint is unsuccessful, then you will have to perform incomplete recovery
Lets do checkpoint to see it completes or not :
SQL> alter system checkpoint ;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open
Checkpoint is unsuccessful, then you will have to perform incomplete recovery :
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE YES 1 41 1820096
2 ACTIVE YES 1 42 1820099
6 ACTIVE YES 1 44 1822759
4 ACTIVE YES 1 45 1823084
5 ACTIVE YES 1 43 1821412
3 CURRENT NO 1 46 1823176
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE YES 1 41 1820096 ---------> Safe
2 ACTIVE YES 1 42 1820099 --------> Damaged
3 CURRENT NO 1 46 1823176 ---------> Safe
4 ACTIVE YES 1 45 1823084 --------> Damaged
5 ACTIVE YES 1 43 1821412 --------> Damaged
6 ACTIVE YES 1 44 1822759 --------> Damaged
In my case I can restore and recover until SCN 1820096. Let's look the following steps:
RMAN> restore database until scn 1820096
RMAN> recover database until scn 1820096
RMAN> alter database open resetlogs;
Statement processed
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 771754072 bytes
Database Buffers 352321536 bytes
Redo Buffers 9383936 bytes
Database mounted.
SQL> Alter database open ;
Alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
MEMBER GROUP# STATUS ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxmxybl_.log 1 INACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxmxy5d_.log 1 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log 2 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log 2 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log 3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log 3 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log 4 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log 4 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log 5 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log 5 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log 6 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log 6 ACTIVE YES
Perform the following steps when restoring an active online redo log group:
• Verify the damage to the members.
• Verify that the status is ACTIVE.
• Attempt to issue a checkpoint. (alter system checkpoint;) If the checkpoint completes successfully, then the active log group should be marked as INACTIVE. A successful checkpoint ensures that all modified database buffers have been written to disk, and at that point, only transactions contained in the CURRENT online redo log will be required for crash recovery.,so you can clear the log group.
• If the log group that was cleared was unarchived, back up your database immediately.
• If the checkpoint is unsuccessful, then you will have to perform incomplete recovery
Lets do checkpoint to see it completes or not :
SQL> alter system checkpoint ;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open
Checkpoint is unsuccessful, then you will have to perform incomplete recovery :
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE YES 1 41 1820096
2 ACTIVE YES 1 42 1820099
6 ACTIVE YES 1 44 1822759
4 ACTIVE YES 1 45 1823084
5 ACTIVE YES 1 43 1821412
3 CURRENT NO 1 46 1823176
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE YES 1 41 1820096 ---------> Safe
2 ACTIVE YES 1 42 1820099 --------> Damaged
3 CURRENT NO 1 46 1823176 ---------> Safe
4 ACTIVE YES 1 45 1823084 --------> Damaged
5 ACTIVE YES 1 43 1821412 --------> Damaged
6 ACTIVE YES 1 44 1822759 --------> Damaged
In my case I can restore and recover until SCN 1820096. Let's look the following steps:
RMAN> restore database until scn 1820096
RMAN> recover database until scn 1820096
RMAN> alter database open resetlogs;
Statement processed
RMAN : Tablespace Never backed up
Scenario 1:
1. Full database + archivelog backup taken last night .
2 . Created new tablespace SUMIT and inserted data by creating a table in that particular tablespace .
SQL> select count(*) from sumit_table ;
COUNT(*)
----------
2048
3. Lost Current control file :
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl
4. Autobackup job not yet complete . So we have no controlfile autoback after addition of new tablespace .
6. Datafile of new tablespace got deleted by mistake
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf
AIM : Get the datafile back for tablespace sumit ;
Alert log :
==========
Tue Mar 08 11:18:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16057.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16076.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:50 2016
1. Try to shutdown the database :
You will not be able to shu immediate because control file is missing . So need to shu abort .
SQL> shu immediate ;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 788531288 bytes
Database Buffers 335544320 bytes
Redo Buffers 9383936 bytes
SQL>
2.Restore the controlfile from last night rman backup :
RMAN> restore controlfile from autobackup ;
Starting restore at 20160308.112954
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
recovery area destination: /data01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
output file name=/data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl
Finished restore at 20160308.112956
3 .Mount the database and report schema to verify if control file is aware of tbs SUMIT : In our case restored control file is not aware of tablespace SUMIT .
RMAN> alter database mount;
using target database control file instead of recovery catalog
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 20160308.113157
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 20160308.113158
Starting implicit crosscheck copy at 20160308.113158
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20160308.113158
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
2 150 TSH_DATA *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
3 700 SYSAUX *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
4 55 UNDOTBS1 *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
6 5 USERS *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_temp_cfxpk4nq_.tmp
4.Restore :
The "RMAN-20202: Tablespace not found in the recovery catalog" and "RMAN-06019: could not translate tablespace name "SUMIT"" errors are thrown, because the controlfile used has no information about that tablespace as you can see in the following output:
RMAN> restore tablespace sumit ;
Starting restore at 20160308.113247
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/08/2016 11:32:47
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "SUMIT"
To recover the missing and "never backed up" tablespace you must restore and recover the entire database.
RMAN> restore database;
Starting restore at 20160308.113325
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp
channel ORA_DISK_1: piece handle=/data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp tag=TAG20160308T103603
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20160308.113420
5 .Recover database :
RMAN> recover database;
Starting recover at 20160308.113501
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc
archived log for thread 1 with sequence 5 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc
archived log for thread 1 with sequence 6 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
archived log for thread 1 with sequence 7 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
archived log for thread 1 with sequence 8 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
archived log for thread 1 with sequence 9 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc thread=1 sequence=4
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
creating datafile file number=5 name=/data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/ o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc thread=1 sequence=6
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc thread=1 sequence=7
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc thread=1 sequence=8
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 20160308.113508
6. Verify Datafile and Data :
[oracle@node2 datafile]$ ls -l
total 1875304
-rw-r----- 1 oracle oper 157294592 Mar 8 11:35 o1_mf_sumit_cfxzsqx7_.dbf
-rw-r----- 1 oracle oper 734011392 Mar 8 11:35 o1_mf_sysaux_cfxpb0jb_.dbf
-rw-r----- 1 oracle oper 807411712 Mar 8 11:35 o1_mf_system_cfxpdor9_.dbf
-rw-r----- 1 oracle oper 62922752 Mar 8 10:35 o1_mf_temp_cfxpk4nq_.tmp
-rw-r----- 1 oracle oper 157294592 Mar 8 11:35 o1_mf_tsh_data_cfxvz9do_.dbf
-rw-r----- 1 oracle oper 57679872 Mar 8 11:35 o1_mf_undotbs1_cfxpj05f_.dbf
-rw-r----- 1 oracle oper 5251072 Mar 8 11:35 o1_mf_users_cfxphz0d_.dbf
SQL> select count(*) from sumit_table ;
COUNT(*)
----------
2048
1. Full database + archivelog backup taken last night .
2 . Created new tablespace SUMIT and inserted data by creating a table in that particular tablespace .
SQL> select count(*) from sumit_table ;
COUNT(*)
----------
2048
3. Lost Current control file :
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl
4. Autobackup job not yet complete . So we have no controlfile autoback after addition of new tablespace .
6. Datafile of new tablespace got deleted by mistake
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf
AIM : Get the datafile back for tablespace sumit ;
Alert log :
==========
Tue Mar 08 11:18:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16057.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:44 2016
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_m000_16076.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Mar 08 11:19:50 2016
1. Try to shutdown the database :
You will not be able to shu immediate because control file is missing . So need to shu abort .
SQL> shu immediate ;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2287528 bytes
Variable Size 788531288 bytes
Database Buffers 335544320 bytes
Redo Buffers 9383936 bytes
SQL>
2.Restore the controlfile from last night rman backup :
RMAN> restore controlfile from autobackup ;
Starting restore at 20160308.112954
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
recovery area destination: /data01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/data01/app/oracle/oradata/ORCL2/controlfile/o1_mf_cfxpjjql_.ctl
output file name=/data01/app/oracle/fast_recovery_area/ORCL2/controlfile/o1_mf_cfxpjk7m_.ctl
Finished restore at 20160308.112956
3 .Mount the database and report schema to verify if control file is aware of tbs SUMIT : In our case restored control file is not aware of tablespace SUMIT .
RMAN> alter database mount;
using target database control file instead of recovery catalog
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 20160308.113157
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 20160308.113158
Starting implicit crosscheck copy at 20160308.113158
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20160308.113158
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/autobackup/2016_03_08/o1_mf_s_905942221_cfxwdxkn_.bkp
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
File Name: /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 770 SYSTEM *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
2 150 TSH_DATA *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
3 700 SYSAUX *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
4 55 UNDOTBS1 *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
6 5 USERS *** /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_temp_cfxpk4nq_.tmp
4.Restore :
The "RMAN-20202: Tablespace not found in the recovery catalog" and "RMAN-06019: could not translate tablespace name "SUMIT"" errors are thrown, because the controlfile used has no information about that tablespace as you can see in the following output:
RMAN> restore tablespace sumit ;
Starting restore at 20160308.113247
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/08/2016 11:32:47
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "SUMIT"
To recover the missing and "never backed up" tablespace you must restore and recover the entire database.
RMAN> restore database;
Starting restore at 20160308.113325
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_tsh_data_cfxvz9do_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp
channel ORA_DISK_1: piece handle=/data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T103603_cfxwc4ds_.bkp tag=TAG20160308T103603
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20160308.113420
5 .Recover database :
RMAN> recover database;
Starting recover at 20160308.113501
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc
archived log for thread 1 with sequence 5 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc
archived log for thread 1 with sequence 6 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc
archived log for thread 1 with sequence 7 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc
archived log for thread 1 with sequence 8 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc
archived log for thread 1 with sequence 9 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_4_cfxwdvgt_.arc thread=1 sequence=4
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
creating datafile file number=5 name=/data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sumit_cfxwfx8b_.dbf
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/ o1_mf_1_5_cfxy1pxh_.arc thread=1 sequence=5
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_6_cfxy1r1d_.arc thread=1 sequence=6
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_7_cfxyx64v_.arc thread=1 sequence=7
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_8_cfxyxv1m_.arc thread=1 sequence=8
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 20160308.113508
6. Verify Datafile and Data :
[oracle@node2 datafile]$ ls -l
total 1875304
-rw-r----- 1 oracle oper 157294592 Mar 8 11:35 o1_mf_sumit_cfxzsqx7_.dbf
-rw-r----- 1 oracle oper 734011392 Mar 8 11:35 o1_mf_sysaux_cfxpb0jb_.dbf
-rw-r----- 1 oracle oper 807411712 Mar 8 11:35 o1_mf_system_cfxpdor9_.dbf
-rw-r----- 1 oracle oper 62922752 Mar 8 10:35 o1_mf_temp_cfxpk4nq_.tmp
-rw-r----- 1 oracle oper 157294592 Mar 8 11:35 o1_mf_tsh_data_cfxvz9do_.dbf
-rw-r----- 1 oracle oper 57679872 Mar 8 11:35 o1_mf_undotbs1_cfxpj05f_.dbf
-rw-r----- 1 oracle oper 5251072 Mar 8 11:35 o1_mf_users_cfxphz0d_.dbf
SQL> select count(*) from sumit_table ;
COUNT(*)
----------
2048
Tuesday, 8 March 2016
Redo failure : Current Redo dropped
Alert log content :
==============
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13908.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 5 thread 1: '/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxr8o6y_.log'
ORA-00312: online log 5 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxr8o9b_.log'
Tue Mar 08 09:36:39 2016
ARC1 started with pid=23, OS id=13922
Tue Mar 08 09:36:39 2016
ARC2 started with pid=24, OS id=13924
Tue Mar 08 09:36:39 2016
Identify the files which are not available :
SQL> select member, a.group#, a.status, a.archived from v$log a, v$logfile b where a.group# = b.group# order by a.group#, member;
MEMBER GROUP# STATUS ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ------- ------- ------- ------- ---- ---- ---
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_cfxpjlf6_.log 1 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_1_cfxpjl8v_.log 1 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxpjnpd_.log 2 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_2_cfxpjnms_.log 2 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_cfxpjpo5_.log 3 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_3_cfxpjpld_.log 3 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_cfxr8hlq_.log 4 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_4_cfxr8hj4_.log 4 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxr8o9b_.log 5 CURRENT NO
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxr8o6y_.log 5 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_6_cfxr8rbl_.log 6 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_6_cfxr8r86_.log 6 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_7_cfxr8vms_.log 7 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_7_cfxr8vk7_.log 7 INACTIVE YES
Unfortunately, we have lost all members of a current online redo log group. Here are some possible options:
• Perform an incomplete recovery up to the last good SCN.
• If flashback is enabled, flash your database back to the last good SCN.
• If you’re using Oracle Data Guard, fail over to your physical or logical standby database.
• Contact Oracle Support for suggestions.
In our case we don't have falshback enabled . So we will perform incomplete recovery upto last good scn .
So let's find the last good SCN :
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by first_change# ;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- ---------------- ---------------- ---------------- -------------
6 INACTIVE YES 1 14 1733941 ------------> Safe
7 INACTIVE YES 1 15 1733945 ------------> Safe
2 INACTIVE YES 1 16 1733948 ------------> Safe
3 INACTIVE YES 1 17 1733951 ------------> Safe
1 INACTIVE YES 1 18 1733954 ------------> Safe
4 INACTIVE YES 1 19 1733957 ------------> Safe
5 CURRENT NO 1 20 1733960 ------------> Damaged
So in our case we can recover the database until 1733960 .
[oracle@node2 2016_03_08]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Mar 8 09:41:29 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=886768067, not open)
RMAN> restore database until sequence 1733960 ;
Starting restore at 20160308.094146
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T092958_cfxrh744_.bkp
channel ORA_DISK_1: piece handle=/data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T092958_cfxrh744_.bkp tag=TAG20160308T092958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 20160308.094233
RMAN> recover database until sequence 1733960 ;
Starting recover at 20160308.094246
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_12_cfxrk0ct_.arc
archived log for thread 1 with sequence 13 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_13_cfxrl4z2_.arc
archived log for thread 1 with sequence 14 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_14_cfxrlcwy_.arc
archived log for thread 1 with sequence 15 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_15_cfxrlf01_.arc
archived log for thread 1 with sequence 16 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_16_cfxrlfqz_.arc
archived log for thread 1 with sequence 17 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_17_cfxrlggq_.arc
archived log for thread 1 with sequence 18 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_18_cfxrlk6w_.arc
archived log for thread 1 with sequence 19 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_19_cfxrln78_.arc
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_12_cfxrk0ct_.arc thread=1 sequence=12
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_13_cfxrl4z2_.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:01
Finished recover at 20160308.094248
RMAN> alter database open resetlogs;
Statement processed
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- ----------------------------------------------------
1 CURRENT NO 1 1 1733961
2 UNUSED YES 1 0 0
3 UNUSED YES 1 0 0
4 UNUSED YES 1 0 0
5 UNUSED YES 1 0 0
6 UNUSED YES 1 0 0
7 UNUSED YES 1 0 0
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 ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ------------------ ------- ------------------ -------
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_cfxs953z_.log 1 CURRENT NO
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_1_cfxs93y1_.log 1 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log 2 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_2_cfxs96bw_.log 2 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_cfxs98t8_.log 3 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_3_cfxs97wp_.log 3 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_cfxs9bvh_.log 4 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_4_cfxs99qc_.log 4 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxs9dx9_.log 5 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxs9crd_.log 5 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_6_cfxs9h4l_.log 6 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_6_cfxs9ftk_.log 6 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_7_cfxs9kly_.log 7 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_7_cfxs9hyy_.log 7 UNUSED YES
==============
Errors in file /data01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13908.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 5 thread 1: '/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxr8o6y_.log'
ORA-00312: online log 5 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxr8o9b_.log'
Tue Mar 08 09:36:39 2016
ARC1 started with pid=23, OS id=13922
Tue Mar 08 09:36:39 2016
ARC2 started with pid=24, OS id=13924
Tue Mar 08 09:36:39 2016
Identify the files which are not available :
SQL> select member, a.group#, a.status, a.archived from v$log a, v$logfile b where a.group# = b.group# order by a.group#, member;
MEMBER GROUP# STATUS ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ------- ------- ------- ------- ---- ---- ---
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_cfxpjlf6_.log 1 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_1_cfxpjl8v_.log 1 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxpjnpd_.log 2 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_2_cfxpjnms_.log 2 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_cfxpjpo5_.log 3 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_3_cfxpjpld_.log 3 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_cfxr8hlq_.log 4 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_4_cfxr8hj4_.log 4 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxr8o9b_.log 5 CURRENT NO
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxr8o6y_.log 5 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_6_cfxr8rbl_.log 6 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_6_cfxr8r86_.log 6 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_7_cfxr8vms_.log 7 INACTIVE YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_7_cfxr8vk7_.log 7 INACTIVE YES
Unfortunately, we have lost all members of a current online redo log group. Here are some possible options:
• Perform an incomplete recovery up to the last good SCN.
• If flashback is enabled, flash your database back to the last good SCN.
• If you’re using Oracle Data Guard, fail over to your physical or logical standby database.
• Contact Oracle Support for suggestions.
In our case we don't have falshback enabled . So we will perform incomplete recovery upto last good scn .
So let's find the last good SCN :
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by first_change# ;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- ---------------- ---------------- ---------------- -------------
6 INACTIVE YES 1 14 1733941 ------------> Safe
7 INACTIVE YES 1 15 1733945 ------------> Safe
2 INACTIVE YES 1 16 1733948 ------------> Safe
3 INACTIVE YES 1 17 1733951 ------------> Safe
1 INACTIVE YES 1 18 1733954 ------------> Safe
4 INACTIVE YES 1 19 1733957 ------------> Safe
5 CURRENT NO 1 20 1733960 ------------> Damaged
So in our case we can recover the database until 1733960 .
[oracle@node2 2016_03_08]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Mar 8 09:41:29 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=886768067, not open)
RMAN> restore database until sequence 1733960 ;
Starting restore at 20160308.094146
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_cfxpdor9_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_cfxpb0jb_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_cfxpj05f_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_cfxphz0d_.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T092958_cfxrh744_.bkp
channel ORA_DISK_1: piece handle=/data01/app/oracle/fast_recovery_area/ORCL2/backupset/2016_03_08/o1_mf_nnndf_TAG20160308T092958_cfxrh744_.bkp tag=TAG20160308T092958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 20160308.094233
RMAN> recover database until sequence 1733960 ;
Starting recover at 20160308.094246
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_12_cfxrk0ct_.arc
archived log for thread 1 with sequence 13 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_13_cfxrl4z2_.arc
archived log for thread 1 with sequence 14 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_14_cfxrlcwy_.arc
archived log for thread 1 with sequence 15 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_15_cfxrlf01_.arc
archived log for thread 1 with sequence 16 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_16_cfxrlfqz_.arc
archived log for thread 1 with sequence 17 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_17_cfxrlggq_.arc
archived log for thread 1 with sequence 18 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_18_cfxrlk6w_.arc
archived log for thread 1 with sequence 19 is already on disk as file /data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_19_cfxrln78_.arc
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_12_cfxrk0ct_.arc thread=1 sequence=12
archived log file name=/data01/app/oracle/fast_recovery_area/ORCL2/archivelog/2016_03_08/o1_mf_1_13_cfxrl4z2_.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:01
Finished recover at 20160308.094248
RMAN> alter database open resetlogs;
Statement processed
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- ----------------------------------------------------
1 CURRENT NO 1 1 1733961
2 UNUSED YES 1 0 0
3 UNUSED YES 1 0 0
4 UNUSED YES 1 0 0
5 UNUSED YES 1 0 0
6 UNUSED YES 1 0 0
7 UNUSED YES 1 0 0
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 ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ------------------ ------- ------------------ -------
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_cfxs953z_.log 1 CURRENT NO
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_1_cfxs93y1_.log 1 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_cfxs974b_.log 2 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_2_cfxs96bw_.log 2 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_cfxs98t8_.log 3 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_3_cfxs97wp_.log 3 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_cfxs9bvh_.log 4 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_4_cfxs99qc_.log 4 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_cfxs9dx9_.log 5 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_cfxs9crd_.log 5 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_6_cfxs9h4l_.log 6 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_6_cfxs9ftk_.log 6 UNUSED YES
/data01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_7_cfxs9kly_.log 7 UNUSED YES
/data01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_7_cfxs9hyy_.log 7 UNUSED YES
Subscribe to:
Posts (Atom)