Friday, 28 February 2014
ARC0: Failed to archive thread 1
ARC1: Failed to archive thread 1 sequence 3425 (19504)
Thu Feb 27 16:45:59 2014
ARC0: Failed to archive thread 1 sequence 3425 (19504)
Thu Feb 27 16:45:59 2014
ARC1: Failed to archive thread 1 sequence 3425 (19504)
Thu Feb 27 16:46:00 2014
ARC0: Failed to archive thread 1 sequence 3425 (19504)
Thu Feb 27 16:46:00 2014
ARC1: Failed to archive thread 1 sequence 3425 (19504)
What is wrong that causes this "Log actively being archived by another process" error?
----------------------
SOLUTION:
----------------------
This error occurs when an archiver background process (ARCHn) wants to archive a log it sees that another ARCH process is already archiving the redo log.
Thursday, 27 February 2014
ORA-01547 ORA-01245 ORA-01110
If Getting this error so try to open resetlogs and noretlogs check all datafile header Chkpt no. is same verify all datafiles..In My case getting this error then kick start the database but after open the db quick take a backup first.
RMAN> recover database.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 2 will be lost if RESETLOGS is done
ORA-01110: data file 2: '/oradb/app/oracle/oradata/orcl/users02.dbf'
NOTE:--- DBA use this own risk after that i dnt knw some data missing or not so before using this match all scn and sequence and verify to all
when database not open resetlog and noresetlog then kick start db
_allow_resetlogs_corruption=true
set on pfile the up but then up after that take a full backup because after that instance will be chrashed.
And co-ordinate with development and your seniors then take the action.
ORA-00279 ORA-00289 ORA-00280 , ORA-00308 ORA-27037 , ORA-01547 ORA-01194 ORA-01110 -- LOSS ALL CONTROLFILE ADD DATAFILE NO BACKUP AVAL.
LOSS ALL CONTROLFILE AND AND ADD DATAFILE NO MORE BACKUP AVAILABLE DATABASE ARCHIVELOG MODE
The same scenario but here we have added a new datafile, created a new user after full backup of database along with controlfile and before
deletion of controlfile.
--Database Status is up and running--
Record the count of below tables before taking cold backup
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp;
COUNT(*)
----------
14
Step 1: Shut the database with "shut immediate" and take cold backup with some os utility like cp etc.
Step 2: Database is Open and made available to users for daily activities
col name format a50
select name,status from v$datafile;
NAME
--------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
Step 3: Add the datafile as shown below
SQL>alter tablespace users add datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend on;
Tablespace altered.
Step 4: Created a user at database level and grant dba role to user as shown below:
SQL> create user abc identified by abc default tablespace users;
User created.
SQL> grant dba to abc;
Grant succeeded.
Connect to newly created user and create tables in it as shown in below steps
SQL> conn abc
Enter password:
Connected.
SQL> create table emp as select * from scott.emp;
Table created.
Note down the count records of newly created table.
SQL> select count(*) from emp;
COUNT(*)
----------
14
After performing above step some user accidentally deleted all the three controlfile at OS level
rm *.ctl
But Users are still unaware of it and normal operations are being carried out as follows:
SQL> create table dept as select * from scott.dept;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>commit;
SQL> alter system switch logfile;
Step 5: We can’t cleanly shut the database with “Shut immediate” so need to shut abort as follows,copy the controlfile from backup to its desired location
and mount the database.
SQL>shut abort;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 100666448 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 499603 generated at 02/03/2014 14:05:03 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_2_838562540.dbf
ORA-00280: change 499603 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502109 generated at 02/16/2014 03:13:32 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_3_838562540.dbf
ORA-00280: change 502109 for thread 1 is in sequence #3
ORA-00278: log file '/oradb/archive/1_2_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502243 generated at 02/16/2014 03:16:05 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_4_838562540.dbf
ORA-00280: change 502243 for thread 1 is in sequence #4
ORA-00278: log file '/oradb/archive/1_3_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502267 generated at 02/16/2014 03:16:12 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_5_838562540.dbf
ORA-00280: change 502267 for thread 1 is in sequence #5
ORA-00278: log file '/oradb/archive/1_4_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502280 generated at 02/16/2014 03:16:15 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 502280 for thread 1 is in sequence #6
ORA-00278: log file '/oradb/archive/1_5_838562540.dbf' no longer needed for
this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradb/app/oracle/oradata/orcl/users02.dbf'
ORA-01112: media recovery not started
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
SQL> alter database rename file '/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/oradb/app/oracle/oradata/orcl/users02.dbf';
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506474 generated at 02/16/2014 05:08:37 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 506474 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
ORA-00278: log file '/oradb/archive/1_6_838562540.dbf' no longer needed for
this recovery
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> select name,status from v$datafile;
NAME STATUS
----------------------------------------------------- -------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf SYSTEM
/oradb/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/example01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users02.dbf RECOVER
6 rows selected.
SQL> alter database datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' online;
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SOLUTION:- FOLLOW RESOLVED THE ISSUE
------------------------------------------------------------------------------------------------------------
Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.
SQL> select name, controlfile_type from v$database ;
NAME CONTROL
------------ ----------------
ORCL BACKUP
SQL> select status,resetlogs_change#,resetlogs_time,checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time,
checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ;
STATUS RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
---------- ----------------------------- ---------------- ------------------------------ -------------------- ----------
ONLINE 464631 03-FEB-14 506996 16-FEB-2014 05:24:09 6
SQL> -- Check for datafile status, and fuzziness ( It means the datafile more recovery to apply the logs on )
SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;
STATUS ERROR FUZ COUNT(*)
---------- --------------------------------------------------------- ------- --------------
ONLINE YES 6
NOTE:-
IF MIN Checkpoint & MAX Checkpoint is different performing for more clarification check datafile
header checkpoint are same or not if not then performing datafile recovery
SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;
MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
506996 506996
SQL> select substr(L.GROUP#,1,6) GROUP#,substr(L.THREAD#,1,7) THREAD#,substr(L.SEQUENCE#,1,10) SEQUENCE#,
substr(L.MEMBERS,1,7) MEMBERS,substr(L.ARCHIVED,1,8) ARCHIVED ,substr(L.STATUS,1,10) STATUS,
substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE# ,substr(LF.member,1,60) REDO_LOGFILE
from GV$LOG L,
GV$LOGFILE LF
where L.GROUP# = LF.GROUP# ;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE# REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1 1 1 1 YES INACTIVE 464631 /oradb/app/oracle/oradata/orcl/redo01.log
3 1 0 1 YES UNUSED 0 /oradb/app/oracle/oradata/orcl/redo03.log
2 1 2 1 NO CURRENT 495046 /oradb/app/oracle/oradata/orcl/redo02.log
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR SCN"
, max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Comment: see CURRENT redo logfile....
In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile.
As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/oradb/app/oracle/oradata/orcl/redo02.log' having SEQ# 7 ( SCN 464631) !
Additional Note:
As this is recover with a Backup Controlfile, or controlfile create from Tracefile (sql> alter database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).
# Options to find the Online log to be used
a: Check the Alert.log file for the last sequences used with 'Online Redolog files'
b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied,
but you will see in in the output message which sequence is in that online redolog file.
Then simply try the next online redolog file until you get 'media recovery complete' message.
c: You may also dump the file log file headers for Online redolog file(s)
Example:
--------
sql> alter system dump logfile '/oradb/app/oracle/oradata/orcl/redo02.log' scn min 1 scn max 1 ;
-- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory
-- Check the tracefile for similar entry like...
~~~
..
thread: 1 nab: 0x3 seq: 0x00000005 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x31fb72ec scn: 0x0000.000716f7 (464631)
..
Low scn: 0x.....
Next scn: 0x.....
..
~~~
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01,log
ORA-00308: cannot open archived log '/oradb/app/oracle/oradata/orcl/redo01,log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 464631 03-FEB-14
3 1 0 52428800 1 YES UNUSED 0
2 1 2 52428800 1 NO CURRENT 495046 03-FEB-14
SQL> alter database open resetlogs ;
Note:
If after applying all archive logs and online redo logs the database does not open
please provide the following script output to Oracle support to assist with the recovery.
( Please upload spooled file: recovery_info.txt )
SQL> set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
NOTE:--- DBA use this own risk after that i dnt knw some data missing or not so before using this match all scn and sequence and verify to all
when database not open resetlog and noresetlog then kick start db
_allow_resetlogs_corruption=true set on pfile the up but then up after that take a full backup because after that instance will be chrashed.
------------------------------------------------------------------------------------------------------------
The same scenario but here we have added a new datafile, created a new user after full backup of database along with controlfile and before
deletion of controlfile.
--Database Status is up and running--
Record the count of below tables before taking cold backup
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp;
COUNT(*)
----------
14
Step 1: Shut the database with "shut immediate" and take cold backup with some os utility like cp etc.
Step 2: Database is Open and made available to users for daily activities
col name format a50
select name,status from v$datafile;
NAME
--------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
Step 3: Add the datafile as shown below
SQL>alter tablespace users add datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend on;
Tablespace altered.
Step 4: Created a user at database level and grant dba role to user as shown below:
SQL> create user abc identified by abc default tablespace users;
User created.
SQL> grant dba to abc;
Grant succeeded.
Connect to newly created user and create tables in it as shown in below steps
SQL> conn abc
Enter password:
Connected.
SQL> create table emp as select * from scott.emp;
Table created.
Note down the count records of newly created table.
SQL> select count(*) from emp;
COUNT(*)
----------
14
After performing above step some user accidentally deleted all the three controlfile at OS level
rm *.ctl
But Users are still unaware of it and normal operations are being carried out as follows:
SQL> create table dept as select * from scott.dept;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>commit;
SQL> alter system switch logfile;
Step 5: We can’t cleanly shut the database with “Shut immediate” so need to shut abort as follows,copy the controlfile from backup to its desired location
and mount the database.
SQL>shut abort;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 100666448 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 499603 generated at 02/03/2014 14:05:03 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_2_838562540.dbf
ORA-00280: change 499603 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502109 generated at 02/16/2014 03:13:32 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_3_838562540.dbf
ORA-00280: change 502109 for thread 1 is in sequence #3
ORA-00278: log file '/oradb/archive/1_2_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502243 generated at 02/16/2014 03:16:05 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_4_838562540.dbf
ORA-00280: change 502243 for thread 1 is in sequence #4
ORA-00278: log file '/oradb/archive/1_3_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502267 generated at 02/16/2014 03:16:12 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_5_838562540.dbf
ORA-00280: change 502267 for thread 1 is in sequence #5
ORA-00278: log file '/oradb/archive/1_4_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502280 generated at 02/16/2014 03:16:15 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 502280 for thread 1 is in sequence #6
ORA-00278: log file '/oradb/archive/1_5_838562540.dbf' no longer needed for
this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradb/app/oracle/oradata/orcl/users02.dbf'
ORA-01112: media recovery not started
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
SQL> alter database rename file '/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/oradb/app/oracle/oradata/orcl/users02.dbf';
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506474 generated at 02/16/2014 05:08:37 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 506474 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
ORA-00278: log file '/oradb/archive/1_6_838562540.dbf' no longer needed for
this recovery
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> select name,status from v$datafile;
NAME STATUS
----------------------------------------------------- -------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf SYSTEM
/oradb/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/example01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users02.dbf RECOVER
6 rows selected.
SQL> alter database datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' online;
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SOLUTION:- FOLLOW RESOLVED THE ISSUE
------------------------------------------------------------------------------------------------------------
Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.
SQL> select name, controlfile_type from v$database ;
NAME CONTROL
------------ ----------------
ORCL BACKUP
SQL> select status,resetlogs_change#,resetlogs_time,checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time,
checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ;
STATUS RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
---------- ----------------------------- ---------------- ------------------------------ -------------------- ----------
ONLINE 464631 03-FEB-14 506996 16-FEB-2014 05:24:09 6
SQL> -- Check for datafile status, and fuzziness ( It means the datafile more recovery to apply the logs on )
SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;
STATUS ERROR FUZ COUNT(*)
---------- --------------------------------------------------------- ------- --------------
ONLINE YES 6
NOTE:-
IF MIN Checkpoint & MAX Checkpoint is different performing for more clarification check datafile
header checkpoint are same or not if not then performing datafile recovery
SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;
MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
506996 506996
SQL> select substr(L.GROUP#,1,6) GROUP#,substr(L.THREAD#,1,7) THREAD#,substr(L.SEQUENCE#,1,10) SEQUENCE#,
substr(L.MEMBERS,1,7) MEMBERS,substr(L.ARCHIVED,1,8) ARCHIVED ,substr(L.STATUS,1,10) STATUS,
substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE# ,substr(LF.member,1,60) REDO_LOGFILE
from GV$LOG L,
GV$LOGFILE LF
where L.GROUP# = LF.GROUP# ;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE# REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1 1 1 1 YES INACTIVE 464631 /oradb/app/oracle/oradata/orcl/redo01.log
3 1 0 1 YES UNUSED 0 /oradb/app/oracle/oradata/orcl/redo03.log
2 1 2 1 NO CURRENT 495046 /oradb/app/oracle/oradata/orcl/redo02.log
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR SCN"
, max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Comment: see CURRENT redo logfile....
In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile.
As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/oradb/app/oracle/oradata/orcl/redo02.log' having SEQ# 7 ( SCN 464631) !
Additional Note:
As this is recover with a Backup Controlfile, or controlfile create from Tracefile (sql> alter database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).
# Options to find the Online log to be used
a: Check the Alert.log file for the last sequences used with 'Online Redolog files'
b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied,
but you will see in in the output message which sequence is in that online redolog file.
Then simply try the next online redolog file until you get 'media recovery complete' message.
c: You may also dump the file log file headers for Online redolog file(s)
Example:
--------
sql> alter system dump logfile '/oradb/app/oracle/oradata/orcl/redo02.log' scn min 1 scn max 1 ;
-- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory
-- Check the tracefile for similar entry like...
~~~
..
thread: 1 nab: 0x3 seq: 0x00000005 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x31fb72ec scn: 0x0000.000716f7 (464631)
..
Low scn: 0x.....
Next scn: 0x.....
..
~~~
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01,log
ORA-00308: cannot open archived log '/oradb/app/oracle/oradata/orcl/redo01,log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 464631 03-FEB-14
3 1 0 52428800 1 YES UNUSED 0
2 1 2 52428800 1 NO CURRENT 495046 03-FEB-14
SQL> alter database open resetlogs ;
Note:
If after applying all archive logs and online redo logs the database does not open
please provide the following script output to Oracle support to assist with the recovery.
( Please upload spooled file: recovery_info.txt )
SQL> set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
NOTE:--- DBA use this own risk after that i dnt knw some data missing or not so before using this match all scn and sequence and verify to all
when database not open resetlog and noresetlog then kick start db
_allow_resetlogs_corruption=true set on pfile the up but then up after that take a full backup because after that instance will be chrashed.
------------------------------------------------------------------------------------------------------------
RMAN-03002 , RMAN-03014 , RMAN-03009 , RMAN-20032 :RMAN CATALOG ERROR
[oradb@database ~]$ rman target sys@orcl catalog=vinay/vinay@dbcata
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 14 22:43:12 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1366343272, not open)
connected to recovery catalog database
RMAN> recover database;
Starting recover at 14-FEB-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/14/2014 22:43:29
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 02/14/2014 22:43:29
RMAN-20032: checkpoint change# too low
-----------------If Incomplete recovery perform the must to update catalog database
rman target sys@orcl catalog=vinay/vinay@dbcata
RMAN> unregister database ORCL;
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> resync catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 02/15/2014 01:01:55
RMAN-20020: database incarnation not set
RMAN> reset database;
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Feb 14 22:43:12 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1366343272, not open)
connected to recovery catalog database
RMAN> recover database;
Starting recover at 14-FEB-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/14/2014 22:43:29
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 02/14/2014 22:43:29
RMAN-20032: checkpoint change# too low
-----------------If Incomplete recovery perform the must to update catalog database
rman target sys@orcl catalog=vinay/vinay@dbcata
RMAN> unregister database ORCL;
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> resync catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 02/15/2014 01:01:55
RMAN-20020: database incarnation not set
RMAN> reset database;
Wednesday, 26 February 2014
ORA-3136: Inbound Connection Timed Out
SYMPTOM
The database alert log reports an ORA-3136 error. Users may also complain that they are unable to log on, although a repeat attempt may prove successful.
ORACLE ERROR (oerr) OUTPUT
error- ora 3136
03136, 00000, "inbound connection timed out"
Cause: Inbound connection was timed out by the server because user authentication was not completed within the given time specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value
Action: 1) Check SQL*NET and RDBMS log for trace of suspicious connections.
2) Configure SQL*NET with a proper inbound connect timeout value if necessary.
---CAUSE
A lag in the network or a layer between the database and the client is preventing the log on process completing within the allowed time.
---SOLUTION
By default, the SQLNET.INBOUND_CONNECT_TIMEOUT is set to 60 seconds.
Change the setting by adding the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_<listener name> to the
{{$ORACLE_HOME/network/admin/sqlnet.ora file on the database server.
Setting the above parameters to a value of 0 implies an infinite time out.
Alternatively, use the lsnrctl command and issue the following:
LSNRCTL> set inbound_connect_timeout=<value>
Before opting for the above parameter changes, confirm that any firewall activity or Network Address Transalation (NAT) that may be occuring beween the client and and the database are not the cause of latency which is exceeding the timeout threshold.
The database alert log reports an ORA-3136 error. Users may also complain that they are unable to log on, although a repeat attempt may prove successful.
ORACLE ERROR (oerr) OUTPUT
error- ora 3136
03136, 00000, "inbound connection timed out"
Cause: Inbound connection was timed out by the server because user authentication was not completed within the given time specified by SQLNET.INBOUND_CONNECT_TIMEOUT or its default value
Action: 1) Check SQL*NET and RDBMS log for trace of suspicious connections.
2) Configure SQL*NET with a proper inbound connect timeout value if necessary.
---CAUSE
A lag in the network or a layer between the database and the client is preventing the log on process completing within the allowed time.
---SOLUTION
By default, the SQLNET.INBOUND_CONNECT_TIMEOUT is set to 60 seconds.
Change the setting by adding the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_<listener name> to the
{{$ORACLE_HOME/network/admin/sqlnet.ora file on the database server.
Setting the above parameters to a value of 0 implies an infinite time out.
Alternatively, use the lsnrctl command and issue the following:
LSNRCTL> set inbound_connect_timeout=<value>
Before opting for the above parameter changes, confirm that any firewall activity or Network Address Transalation (NAT) that may be occuring beween the client and and the database are not the cause of latency which is exceeding the timeout threshold.
Tuesday, 25 February 2014
IP SET ON SOLARIS MACHINE
System Administration
Guide: IP Services
gedit /etc/hosts
192.168.56.xx database database.com
gedit /etc/nodename
database
gedit
/etc/hostname.e1000g0 //lan name
database
putty login
1. add user then login through user
groupadd oinstall
groupadd dba
groupadd oper
useradd –g oinstall –G dba
–m –d /export/home/oracle oracle
passwd oraclesvcamd restart ssh
2. alternate
gedit /etc/ssh/sshd_config
PermitRootLogin yes
How
to Add a Physical Interface After Installation in Solaris 10 3/05 ONLY
Note –
The next procedure
contains applies to users of the Solaris 10 3/05 OS only. If you are using an
update to Oracle Solaris 10, refer to How to Configure a Physical Interface After System
Installation.
how many physical network card on your machine, you should run
# dladm show-link
You will see hidden card which not yet plumb up. After that you can plum up by run
# ifconfig plumb up
And then
# ifconfig netmask
1. On the system with the interfaces to be configured, assume the Primary Administrator role or become superuser.
# dladm show-link
You will see hidden card which not yet plumb up. After that you can plum up by run
# ifconfig plumb up
And then
# ifconfig netmask
1. On the system with the interfaces to be configured, assume the Primary Administrator role or become superuser.
The Primary Administrator role includes the Primary
Administrator profile. To create the role and assign the role to a user, see Chapter 2, Working With the Solaris Management Console
(Tasks), in System Administration Guide: Basic Administration.
# ifconfig interface plumb up
|
For example, for qfe0 you would type:
# ifconfig hostname.e1000g0 plumb up
Note –
Interfaces that are
explicitly configured with the ifconfig command do not
persist across a reboot.
3.
Assign an IPv4 address and netmask to the interface.
# ifconfig interface IPv4-address netmask+netmask
|
For example, for qfe0
you would type:
# ifconfig hostname.e1000g0
10.0.0.32 netmask + 255.255.255.0
|
4.
Verify that the newly configured interfaces are plumbed and
configured, or “UP.”
# ifconfig –a
|
Check the status line
for each interface that is displayed. Ensure that the output contains an UP
flag on the status line, for example:
qfe0:
flags=1000843 <UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
|
5.
(Optional) To make the interface configuration persist across
reboots, perform the following steps:
For example, to add a qfe0 interface, you would
create the following file:
# vi /etc/hostname.qfe0
|
b.
Edit the /etc/hostname.interface file.
At a minimum, add the IPv4 address of the interface to the file.
You can also add a netmask and other configuration information to the file.
Note –
To add an IPv6 address
to an interface, refer to Modifying an IPv6 Interface Configuration for Hosts and
Servers
c.
Add entries for the new interfaces into the /etc/inet/hosts file.
d.
Perform a reconfiguration boot.
# reboot -- -r
|
e.
Verify that the interface you created in the /etc/hostname.interface file has been configured.
# ifconfig –a
|
Example 5–10
Configuring an Interface After System Installation
The following example
shows how to add two interfaces, qfe0 and qfe1. These interfaces are
attached to the same network as the primary network interface, hme0.
Note that this interface configuration exists until you reboot the system. For
an example that shows how to make interface configurations persist across reboots,
see Example 6–2. However, the dladm
command that is used in that example is only available starting with the
Solaris 10 1/06 OS.
# ifconfig qfe0 plumb up
# ifconfig qfe1 plumb up
# ifconfig qfe0 10.0.0.32 netmask 255.0.0.0
# ifconfig qfe1 10.0.0.33 netmask 255.0.0.0
|
# ifconfig -a
lo0:
flags=1000849 <UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1
inet 127.0.0.1 netmask ff000000
hme0:
flags=1000843 <UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
inet 10.0.0.14 netmask ff000000
broadcast 10.255.255.255
ether 8:0:20:c1:8b:c3
qfe0:
flags=1000843 <UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 3
inet 10.0.0.32 netmask ff000000
broadcast 10.255.255.255
ether 8:0:20:c8:f4:1d
qfe1:
flags=1000843 <UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 4
inet 10.0.0.33 netmask ff000000
broadcast 10.255.255.255
ether 8:0:20:c8:f4:1e
|
See Also
·
To configure an IPv6 address onto an interface, refer to How to Enable an IPv6 Interface for the Current Session.
·
To set up failover detection and failback for interfaces using
Network Multipathing (IPMP), refer to Chapter 31, Administering IPMP (Tasks).
Note –
The next procedure
contains applies to users of the Solaris 10 3/05 OS only. If you are using an
update to Oracle Solaris 10, refer to How to Remove a Physical Interface.
1.
On the system with the interface to be removed, assume the
Primary Administrator role, or become superuser.
The Primary Administrator role includes the Primary
Administrator profile. To create the role and assign the role to a user, see Chapter 2, Working With the Solaris Management Console
(Tasks), in System Administration Guide: Basic Administration.
Use the following form of the ifconfig command:
# ifconfig interfacedown unplumb
|
For example, you would remove the interface eri1
as follows:
# ifconfig eri1 down unplumb
|
Subscribe to:
Posts (Atom)