FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      D:\romford\oradata
db_recovery_file_dest                string      D:\romford\flash_recovery_area
db_recovery_file_dest_size           big integer 3G
db_flashback_retention_target        integer     1440
recyclebin                           string      on
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter tablespace UNDOTBS1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION
—————————— ———–
SYSTEM                         NOT APPLY
SYSAUX                         NOT APPLY
UNDOTBS1                       GUARANTEE
TEMP                           NOT APPLY
USERS                          NOT APPLY
MPOOJARI                       NOT APPLY

To flash back a table to an earlier SCN or timestamp, you must have either the

1. FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3. Row movement must be enabled for all tables in the Flashback list

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as “flashback transaction queries” and require the SELECT ANY TRANSACTION system privilege.

Error: Selecting from FLASHBACK_TRANSACTION_QUERY gives you null values in UNDO_SQL and OPERATION is set to ‘UNKNOWN’.

Its not that there wasn’t sufficient undo but it was not logged because of supplemental logging not enabled. This is a change which is there from 11g onwards. To make sure that the completel information is logged in the FLASHBACK_TRANSACTION_QUERY view, you must enable it. You can do so by,

SQL> alter database add supplemental log data;

Database altered.

Advertisements

RMAN Data Recovery Advisor

The Data Recovery Advisor automatically diagnoses corruption or loss of persistent data on disk, determines the appropriate repair options, and executes repairs at the user’s request. This reduces the complexity of recovery process, thereby reducing the Mean Time To Recover (MTTR).

  • Create a Failure
  • Relevant RMAN Functionality
  1. LIST FAILURE
  2. ADVISE FAILURE
  3. REPAIR FAILURE
  4. CHANGE FAILURE
  5. VALIDATE

CREATE FAILURE
I forcibly renamed one of my tablespace {{‘mpoojari01.dbf’}} to {{‘mpoojari01test.dbf’}}. As a result, database went into MOUNT state and did not open.
Files at OS level

D:\romford\oradata\ROMFORD\DATAFILE>dir
Volume in drive D is mpoojari
Volume Serial Number is 74B1-F62B

Directory of D:\romford\oradata\ROMFORD\DATAFILE

10/02/2011  16:31    <DIR>          .
10/02/2011  16:31    <DIR>          ..
10/02/2011  16:27       104,865,792 MPOOJARI01test.DBF <==renamed for this training
10/02/2011  16:27       629,153,792 O1_MF_SYSAUX_6M82W55S_.DBF
10/02/2011  16:27       734,011,392 O1_MF_SYSTEM_6M82VLDJ_.DBF
10/02/2011  16:19        71,311,360 O1_MF_TEMP_6M82WXQ2_.TMP
10/02/2011  16:27       419,438,592 O1_MF_UNDOTBS1_6M82WPDT_.DBF
10/02/2011  16:27         5,251,072 O1_MF_USERS_6M82XP48_.DBF
10/02/2011  16:32        52,429,312 STBYREDO04.LOG
10/02/2011  16:32        52,429,312 STBYREDO05.LOG
10/02/2011  16:32        52,429,312 STBYREDO06.LOG
10/02/2011  16:32        52,429,312 STBYREDO07.LOG
11 File(s)  2,278,615,040 bytes
2 Dir(s)  44,868,362,240 bytes free

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn mpoojari/mpoojari
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.
SQL> quit

D:\romford\oradata\ROMFORD\DATAFILE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 16:28:35 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
ROMFORD   MOUNTED

LIST FAILURE

The LIST FAILURE command displays any failures with a status OPEN and a priority of CRITICAL or HIGH in order of importance. If no such failures exist it will list LOW priority failures.

RMAN> LIST FAILURE;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1862       HIGH     OPEN      10-FEB-11     One or more non-system datafiles are
missing

ADVISE FAILURE
The ADVISE FAILURE command, as the name implies, provides repair advice for failures listed by the LIST FAILURE command, as well as closing all open failures that are already repaired.

RMAN> ADVISE FAILURE;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
1862       HIGH     OPEN      10-FEB-11     One or more non-system datafiles are
missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJARI01.DBF was unintentionall
y renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_3123
413256.hm

RMAN>

REPAIR FAILURE

The REPAIR FAILURE command applies the repair scripts produced by the ADVISE FAILURE command. Using the PREVIEW option lists the contents of the repair script without applying it.

RMAN> REPAIR FAILURE PREVIEW;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_312341
3256.hm

contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;

By default, the REPAIR FAILURE command prompts the user to confirm the repair, but this can be prevented using the NOPROMPT keyword. Make sure the database in MOUNT mode.

RMAN> REPAIR FAILURE NOPROMPT;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\romford\app\mpoojari\diag\rdbms\romford\romford\hm\reco_312341
3256.hm

contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;
executing repair script

Starting restore at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2

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 00005 to D:\ROMFORD\ORADATA\ROMFORD\DATAF
ILE\MPOOJARI01.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUPS\ROMFORD\ROMFORD_2QM4AB4
L_1_1
channel ORA_DISK_1: piece handle=D:\BACKUPS\ROMFORD\ROMFORD_2QM4AB4L_1_1 tag=TAG
20110210T100820
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 10-FEB-11

Starting recover at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 10-FEB-11
repair failure complete
database opened

RMAN>

CHANGE FAILURE

The CHANGE FAILURE command allows you to change the priority of a failure or close an open failure. You may wish to change the priority of a failure if it does not represent a problem to you. For example, a failure associated with a tablespace you know longer use may be listed as a high priority, when in fact it has no effect on the normal running of your system.

RMAN> CHANGE FAILURE 1862 PRIORITY LOW;

no failures found that match specification

VALIDATE
The VALIDATE command initiates data integrity checks, logging physical, and optionally logical, block corruptions of database files and backups in the V$DATABASE_BLOCK_CORRUPTION view and the Automatic Diagnostic Repository as one or more failures.

RMAN> VALIDATE CHECK LOGICAL TABLESPACE MPOOJARI;

Starting validate at 10-FEB-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJA
RI01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5    OK     0              12665        12800           1088851
File Name: D:\ROMFORD\ORADATA\ROMFORD\DATAFILE\MPOOJARI01.DBF
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data       0              5
Index      0              0
Other      0              130

Finished validate at 10-FEB-11

Files at OS level

D:\romford\oradata\ROMFORD\DATAFILE>dir
Volume in drive D is mpoojari
Volume Serial Number is 74B1-F62B

Directory of D:\romford\oradata\ROMFORD\DATAFILE

10/02/2011  16:31    <DIR>          .
10/02/2011  16:31    <DIR>          ..
10/02/2011  16:40       104,865,792 MPOOJARI01.DBF <==after repair
10/02/2011  16:27       104,865,792 MPOOJARI01test.DBF <====old deleted or renamed file
10/02/2011  16:27       629,153,792 O1_MF_SYSAUX_6M82W55S_.DBF
10/02/2011  16:27       734,011,392 O1_MF_SYSTEM_6M82VLDJ_.DBF
10/02/2011  16:19        71,311,360 O1_MF_TEMP_6M82WXQ2_.TMP
10/02/2011  16:27       419,438,592 O1_MF_UNDOTBS1_6M82WPDT_.DBF
10/02/2011  16:27         5,251,072 O1_MF_USERS_6M82XP48_.DBF
10/02/2011  16:32        52,429,312 STBYREDO04.LOG
10/02/2011  16:32        52,429,312 STBYREDO05.LOG
10/02/2011  16:32        52,429,312 STBYREDO06.LOG
10/02/2011  16:32        52,429,312 STBYREDO07.LOG
11 File(s)  2,278,615,040 bytes
2 Dir(s)  44,868,362,240 bytes free

D:\romford\oradata\ROMFORD\DATAFILE>sqlplus mpoojari/mpoojari

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 16:41:30 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col name for a20
SQL> select * from test;

ROLLNO NAME
———- ——————–
1 Mohan Poojari
2 mpoojari2
3 mpoojari12
4 mpoojari4
5 mpoojari5
6 mpoojari6
7 mpoojari7
8 mpoojari8

8 rows selected.

VALIDATE

# Check for physical corruption of all database files.
VALIDATE DATABASE;

# Check for physical and logical corruption of a tablespace.
VALIDATE CHECK LOGICAL TABLESPACE USERS;

# Check for physical and logical corruption of a datafile.
VALIDATE CHECK LOGICAL DATAFILE 4;

# Check for physical corruption of all archived redo logs files.
VALIDATE ARCHIVELOG ALL;

# Check for physical and logical corruption of the controlfile.
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;

# Check for physical and logical corruption of a specific backupset.
VALIDATE CHECK LOGICAL BACKUPSET 3;

The BACKUP VALIDATE and RESTORE VALIDATE commands perform the same checks as the VALIDATE command for the files targeted by the backup or restore command, but they don’t actually perform the specified backup or restore operation. This allows you to check the integrity of a backup or restore operation before actually performing it. The following code shows some of the possible syntax variations.

# Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

# Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

# Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;

References:

ID 762339.1, ID 465946.1,

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmrepai.htm#BRADV246

RMAN 11gR1 : New Features [ID 809867.1]

OCFS2 1.2 – FAQ

Any special flags to run Oracle RAC?

OCFS2 volumes containing the Voting diskfile (CRS), Cluster registry (OCR), Data files, Redo logs, Archive logs and Control files must be mounted with the datavolume and nointr mount options. The datavolume option ensures that the Oracle processes opens these files with the o_direct flag. The nointr option ensures that the ios are not interrupted by signals.

    	# mount -o datavolume,nointr -t ocfs2 /dev/sda1 /u01/db
    

For more FAQ, peep into http://oss.oracle.com/projects/ocfs2/dist/documentation/v1.2/ocfs2_faq.html

Disable DBMS Scheduler jobs on startup

My intention is to disable all dbms jobs prior opening the database.

Set job_queue_processes = 0 in pfile. Create the spfile on ASM or NFS.

Duplicate Database With RMAN Without Connecting To Target Database [ID 732624.1].

Manually, do

  1. Rman Controlfile restore
  2. Perform Manual restore and recovery

Startup the database in restricted mode (resetlogs upgrade)

alter database open resetlogs upgrade;

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

Also, run  the output of the following SQL to disable jobs explicitly:

SELECT 'EXEC dbms_scheduler.disable('||chr(39)|| owner ||'."'|| job_name || '"'||chr(39) ||',TRUE);'
     from dba_scheduler_jobs
     where owner not in ('SYS','SYSTEM','EXFSYS','ORACLE_OCM') order by owner;

Note that only connect AS SYSDBA is allowed when OPEN in UPGRADE mode. hence do the following:

@$ORACLE_HOME/rdbms/admin/utlirp.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

shutdown immediate;

startup;
If startup do not happen due the error in identifying the controlfile, then

  1. check the new controfile names that has been created,
  2. update them in the PFILE,
  3. startup database using PFILE
  4. Recreate SPFILE
  5. shutdown immediate
  6. startup

Optional Steps:

  1. Rename the database using NID in the end, if required.
  2. Disable the archive log mode

Cheers. Have fun.

RMAN-03002 RMAN-11003 ORA-19906

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/19/2010 14:44:57
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘/u02/oracle/oraarch/TOTEMD/archivelog/2010_05_19/o1_mf_1_26104_5z7ttqww_.arc’
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery

The database refresh failed during recovery. Restore went all good.
Cause:
The backup control file is picking up the wrong incarnation
Solution:
list incarnation;
#Change the database incarnation
RESET DATABASE TO INCARNATION <x>;
recover database until cancel using backup controlfile;
alter database open resetlogs;

RMAN Recovery Session Fails with ORA-1861

RMAN Recovery Session Fails with ORA-1861

RMAN> recover database;

Starting recover at 03-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=526 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=533 devtype=DISK

starting media recovery

archive log thread 2 sequence 20034 is already on disk as file +FRA/sngprd/archivelog/2010_02_28/thread_2_seq_20034.4044.712175745
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/03/2010 11:52:19
ORA-01861: literal does not match format string

Cause:Bug 8513905: ORA-1861 DURING RMAN RECOVERY

Backuppieces in the controlfile have an invalid date.

Solution:

From the provided information, it was indeed I was hitting the issue as reported in Note 852723.1.

Therefore, the proposed workaround to manually delete the reference to this backuppiece using package
dbms_backup_restore.deleteBackupPiece should be implemented.

After the reference has been deleted, you can re catalog the backuppiece. I have tried this and it did work.

Checks

To check if you are hitting this bug query the controlfile:

SQL>alter session set nls_date_format=’dd-mon-rr hh24:mi:ss;
SQL>select recid, status, device_type, handle, completion_time from v$backup_piece where completon_time > ‘<date>’;

Use ‘<date>’ to limit the output to those backuppieces being used during recovery and check for invalid dates.   In this case, completion_time for the backuppiece was 04/31/2009 00:57:33 (April only has 30 days).

If the SQL above raises ORA-1861, dump the controlfile and find the trace file in target udump directory :

SQL>alter session set events ‘trace name controlf level 9’;

Look for a the BACKUP PIECE RECORDS section eg

@ ***************************************************************************

Workaround:

1. Recreate the controlfile.

2. An alternative to recreating the catalog would be to first
identify all backup pieces with incorrect completion time, using a full
controlfile dump), then for each backup piece with incorrect completion time
do:

SQL>select recid, stamp, handle, set_stamp, set_count, piece#
from v$backup_piece where recid = nnnnn;

WARNING: Before doing the next step, copy the backup piece to another
location on disk, otherwise it will be deleted, so for example:

$ cp <handle>  <handle_copy_2>

Call directly (as user SYS) dbms_backup_restore.deleteBackupPiece using the values obtained
from the select:

SQL>exec dbms_backup_restore.deleteBackupPiece(recid, stamp, ‘handle’,
set_stamp, set_count, piece#);

After all offending pieces have been deleted, then the copied pieces can be
recataloged (but not near the end of a month that does not have 31 days) and
the catalog resynced.