Oracle DBA & Data Science Enthusiast

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.

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

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

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

Saturday, 5 March 2016

Predicate Selectivity

Selectivity
~~~~~~~~~~~
Selectivity is a measure of the proportion of a row source retrieved by application of a particular predicate or combination of predicates.

Within the Oracle kernel it is expressed as a value between 0 and 1. The closer the value is to 0 the more selective the predicate is.
Selectivity is only used by the CBO.

Basic Selectivity formula:
~~~~~~~~~~~~~~~~~~~~~~~~~~

Number of records satisfying a condition
Selectivity = -----------------------------------------
Total Number of records

In the optimizer, selectivity is used to compare the usefulness of various predicates in combination with base object costs.

Knowing the proportion of the total data set that a column predicate defines is very helpful in defining actual access costs.

By default, column selectivity is based on the high and low values and the number of values in the column with an assumption of even distribution of data between these two points.

Histogram data can give better selectivity estimates for unevenly distributed column data. There is more discussion regarding Histograms later.

Selectivity is also used to define the cardinality of a particular row source once predicates have been applied.

Cardinality is the expected number of rows that will be retrieved from a row source. Cardinality is useful in determining nested loop join and sort costs. Application of selectivity to the original cardinality of the row source will produce the expected (computed) cardinality
for the row source.

Glossary of Terms:
~~~~~~~~~~~~~~~~~~

NDV Number of Distinct Values
Cardinality Number of rows
Selectivity Proportion of a dataset returned by a particular predicate(or group of predicates)

In the following illustrations there are 2 tables (T1 & T2) with columns (c1) and (c2) respectively.

Selectivities:
~~~~~~~~~~~~~~
Without histograms
~~~~~~~~~~~~~~~~~~
c1 = '4076' 1/NDV
c1 > '4076' 1 - (High - Value / High - Low)
c1 >= '4076' 1 - (High - Value / High - Low) + 1/NDV
c1 like '4076' 1/NDV

Join selectivity
~~~~~~~~~~~~~~~~

The selectivity of a join is defined as the selectivity of the most selective
join column adjusted by the proportion of not null values in each join column.

Sel = 1/max[NDV(t1.c1),NDV(t2.c2)] *
( (Card t1 - # t1.c1 NULLs) / Card t1) *
( (Card t2 - # t2.c2 NULLs) / Card t2)

Bind Variable selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~

Bind variables present a special case because the optimizer has no idea what
the bind variable value is prior to query optimization. This does not present
a problem with equality predicates since a uniform distribution of data is
assumed and the selectivity is taken as 1/NDV for the column. However for
range predicates it presents a major issue because the optimizer does not
know where the range starts or stops. Because of this the optimizer has to
make some assumptions as follows:

c1 = :bind1 1/NDV
c1 > :bind1 Default of 5%
c1 >= :bind1 Default of 5%
c1 like :bind1 Default of 25%

For more information on bind variables see Note:70075.1

Selectivity With Histograms
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Histograms provide additional information about column selectivity for columns whose distribution is non uniform. Histograms store information about column data value ranges. Each range is stored in a single row and is often called a 'bucket'. There are 2 different methods for storing histograms in
Oracle. If there are a small number of distinct column values (i.e. less than the number of buckets), the column value and the count of that value is stored. If not then a series of endpoints are stored to enable more accurate selectivity to be determined.

The first method allows the accurate figures to be used. However with inexact histograms the terms popular and non-popular value are introduced and are used to help determine selectivity. A popular value is a value that spans multiple endpoints whereas a non-popular value does not.
See Note:72539.1 for more information on histograms.

Exact histograms
~~~~~~~~~~~~~~~~
c1 = '4706' count of value '4076' / Total Number of Rows
c1 > value count of values > '4076' / Total Number of Rows

In Exact Histograms
~~~~~~~~~~~~~~~~~~
col = pop value # popular buckets / # buckets
col = non pop (Density)
col > value # buckets > value / # buckets

Rules for combining selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Let P1 and P2 be 2 distinct predicates of query Q

P1 AND P2
S(P1&P2) = S(P1) * S(P2)
P1 OR P2
S(P1|P2) = S(P1) + S(P2) -[S(P1) * S(P2)]

Index Selectivity for concatenated indexes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Starting with 10.2, when a concatenated index, with all its columns having equality predicates, is used as an access path, the optimizer uses 1/NDK as the selectivity (where NDK is the number of distinct keys in the index).

On 9.2.0.7 and 9.2.0.8 this may be enabled with event 38060 level 1.
On 10.1.0.4 and 10.1.0.5 this may be enabled with event 38059 level 1.
On 10.2 adjustments will be made to the selectivity to account for nulls
in the index keys. This also occurs on 10.1.0.5 (with event 38059.)

Join cardinality
~~~~~~~~~~~~~~~~

Card(Pj) = Card(T1) * Card(T2) * Sel(Pj)

+++ Reference : Oracle support Metalink +++

Friday, 4 March 2016

Why Text Search Works for Old Indexed Data but Not for all New Added Data

APPLIES TO:

Oracle Text - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
GOAL

How to implement Text index maintenance strategy for non-transactional context indexes?

SOLUTION

When documents in the base table are inserted, updated, or deleted, their ROWIDs are held in a DML queue until you synchronize the index. You can view this queue with the CTX_USER_PENDING view.

For example, to view pending DML on all your indexes, issue the following statement as index owner:

SELECT pnd_index_name, pnd_partition_name, pnd_rowid, to_char(pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp FROM ctx_user_pending;

1. Synchronizing the index involves processing all pending updates, inserts, and deletes to the base table. You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure.

The following example synchronizes the index with 50 megabytes of memory:

begin
ctx_ddl.sync_index('<index_name>', '50M');
end;
/

2. Or use the automatic sync feature and specify that the index be synchronized at regular intervals. To do so, create the index with the SYNC (EVERY "interval-string") parameter.

To use job scheduling, you must log in as a user who has DBA privileges and then grant CREATE JOB privileges.

CONNECT system/passwd@tns_alias
GRANT CREATE JOB TO <index_owner>;

CONNECT <index_owner>/passwd@tns_alias
alter index <index_name> rebuild parameters ('replace metadata sync (every "SYSDATE+5/1440")');

Or create a job through Oracle Scheduler (the Scheduler) that synchronizes the text index every 5 minutes.
Example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'sync_myindex1',
job_type => 'PLSQL_BLOCK',
job_action => 'ctx_output.start_log(''sync_myindex1.log''); ctx_ddl.sync_index(''MYINDEX1'', ''50M''); ctx_output.end_log();',
start_date => SYSDATE,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;
comments => 'syncing index MYINDEX1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME ENABLED
----------------------------- -------
SYNC_MYINDEX1 FALSE

BEGIN
DBMS_SCHEDULER.ENABLE ('sync_myindex1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME ENABLED
----------------------------- -------
SYNC_MYINDEX1 TRUE

SELECT JOB_NAME, STATUS FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'SYNC_MYINDEX1';

JOB_NAME STATUS
--------------------------------------- ------------------------------
SYNC_MYINDEX1 SUCCEEDED

Frequent index synchronization can fragment your CONTEXT index. Index fragmentation can adversely affect query response time. You can optimize your CONTEXT index to reduce fragmentation and index size and so improve query performance.

With the CTX_REPORT.INDEX_STATS procedure, you can create a statistical report on your index. The report includes information on optimal row fragmentation, list of most fragmented tokens, and the amount of garbage data in your index. Although this report might take long to run for large indexes, it can help you decide whether to optimize your index.

To optimize an index, Oracle recommends that you create a job through the Scheduler and run CTX_DDL.OPTIMIZE_INDEX.

Example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'opt_rebuild_myindex1',
job_type => 'PLSQL_BLOCK',
job_action => 'ctx_output.start_log(''opt_myindex1.log''); ctx_ddl.optimize_index(''MYINDEX1'',''REBUILD''); ctx_output.end_log();',
start_date => SYSDATE,
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN;', /* every sunday */
comments => 'optimize index MYINDEX1 in rebuild mode');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME ENABLED
------------------------------ -------
OPT_REBUILD_MYINDEX1 FALSE

BEGIN
DBMS_SCHEDULER.ENABLE ('opt_rebuild_myindex1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME ENABLED
------------------------------ -------
OPT_REBUILD_MYINDEX1 TRUE

SELECT JOB_NAME, STATUS FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'OPT_REBUILD_MYINDEX1';

JOB_NAME STATUS
------------------------------ --------------------
OPT_REBUILD_MYINDEX1 SUCCEEDED

Wednesday, 2 March 2016

Flashback Interview Questions

Q: Is there a separate background process for writing flashback logs?

A: Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

Q: Do I need to shutdown and mount the database to turn flashback on?

A: ALTER DATABASE FLASHBACK ON is an online operation in 11g Release 2. In 10g Release 2 the database must be mounted, but not open.

Once flashback is turned on Oracle starts to save the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.

Q: What happens if RVWR cannot write to disk?

A: It depends on the context where the write error occurs:

If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.

If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.

If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.

Q: Is it possible to specify the size of the Flashback Buffer in the SGA?

A: Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.

For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for

databases running in Flashback Mode.

UPDATE: For large 11.1.0.7+ databases with more than a 4GB SGA, you may consider setting LOG_BUFFER to values in the range of 32-64 MB.

Q: Can RMAN be used to backup flashback logs?

A: No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.

Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.

Q: When are the flashback logs deleted?

A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.

If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.

If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.

If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.

If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

Other than that flashback logs are deleted according to the below:

When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.

When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

Q: How to list restore points in RMAN?

A: In RMAN you can use the LIST RESTORE POINT [ALL|restore_point_name] command. If you use a recovery catalog you can use the view RC_RESTORE_POINT in the recovery catalog repository, or the command the V$RESTORE_POINT in the target database..

Q: After flashback’ing to a point-in-time before a RESETLOGSoperation is it possible to flash forward to the incarnation after theRESETLOGS?

A: Yes, it’s perfectly possible.

Q: Can you see the progress of a FLASHBACK DATABASE operation?

A: Yes, you can. During a FLASHBACK DATABASE operation you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.

The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.

While the actual flashback is running you’ll see the following message in V$SESSION_LONGOPS, on Oracle 11gR2:

Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done

During the media recovery, the following messages will be seen:

Media Recovery: Redo Applied : 263 out of 0 Megabytes done

Media Recovery: Average Apply Rate : 1164 out of 0 KB/sec done

Media Recovery: Last Applied Redo : 626540 out of 0 SCN+Time done

Media Recovery: Elapsed Time : 232 out of 0 Seconds done

Media Recovery: Active Time : 116 out of 0 Seconds done

Media Recovery: Active Apply Rate : 1859 out of 0 KB/sec done

Media Recovery: Maximum Apply Rate : 1859 out of 0 KB/sec done

Media Recovery: Log Files : 15 out of 0 Files done

Media Recovery: Apply Time per Log : 7 out of 0 Seconds done

Q: How should I set the database to improve Flashback performance?

A: Oracle’s recommendations are:

Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.

Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.

If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance

For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

Reference : https://www.pythian.com/blog/questions-you-always-wanted-to-ask-about-flashback-database/

Total Pageviews