11g flash back data archive feature and error code ora-959,ora-949 – for the new oracle dba




you are a new oracle dba and you would like to know about the following things

a)ora-959 , ora-942,ora-1536 in FDBA trace file(flash back trace files)

b)what is meant by FDA?(flashback data archive)

The answers to the above questions can be found in the below metalink documents.

 

11g feature: Flashback Data Archive Guide. [ID 470199.1]

  Modified 27-JUL-2011     Type BULLETIN     Status PUBLISHED  

In this Document
Purpose
Scope and Application
11g feature: Flashback Data Archive Guide.
Note:
References


Applies to:

Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.6 – Release: 11.1 to 11.1
Information in this document applies to any platform.

Purpose

  • What is FDA (Flashback Data Archive) ?
  • How to enable Flashback Data Archive features?
  • What is the role of the new background process FBDA?
  • What is the Flashback Data Archive Workflow an Example?
  • How to configure a Default Flashback Data Archive as Example?
  • What is Flashback Data Archive Restrictions?

 

Scope and Application

This feature can be used by DBA’s and Support Analysts to understand and
configure Flashback Data Archive

11g feature: Flashback Data Archive Guide.

What is FDA (Flashback Data Archive) ?
A new database object, a flashback data archive is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.

You specify retention duration for each flashback data archive (could be # of years).

SQL> create flashback archive fla1 tablespace tbs1 retention 2 year;

With the “Oracle Total Recall” option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup . This efficiency cannot be duplicated by your own triggers, which also cost time and effort to set up and maintain.

To satisfy long-retention requirements that exceed the undo retention, create a flashback data archive.

How to enable Flashback Data Archive features?
To satisfy long-retention requirements, use flashback data archives. Begin with the following steps:

• for your archive administrator:
1– Create one or more tablespaces for data archives and grant QUOTA on the tablespaces.
2– Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to create and maintain flashback archives.

• For archive users:
Grant the FLASHBACK ARCHIVE object privilege (to enable history tracking for specific tables in the given flashback archives).

>SCOTT (Archive user)

SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

As SYS use or Archive Administrator (who granted FLASHBACK ARCHIVE ADMINISTER system privilege)

SQL> grant flashback archive on fla1 to scott;

Grant succeeded

Note:
Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to your archive administrator, to allow execution of the following statements:
• CREATE FLASHBACK ARCHIVE
• ALTER FLASHBACK ARCHIVE
• DROP FLASHBACK ARCHIVE

What is the role of the new background process FBDA?
The Flashback Data Archive background process, FBDA, starts with the database.
1. FBDA operates first on the undo in the buffer cache.
2. In case the undo has already left the buffer cache, FBDA could also read the required values from the undo segments.
3. FBDA consolidates the modified rows of flashback archive–enabled tables and writes them into the appropriate history tables, which make up the flashback data
archive.

You can find the internally assigned names of the history tables by querying the *_FLASHBACK_ARCHIVE_TABLES view. History tables are compressed and internally partitioned. The database automatically purges all historical information on the day after the retention period expires. (It deletes the data, but does not destroy the flashback data archive.) For example, if the retention period is 10 days, then every day after the tenth day, the oldest information is deleted; thus
leaving only 10 days of information in the archive. This is a way to implement digital shredding.

Waht is the Flashback Data Archive Workflow an Exmaple?
1. Create the flashback data archive:

The archive administrator creates a flashback data archive named fla1, which uses up to unlimited of the tbs1 tablespace and whose data will be retained for two years. If you want to set up your database so that information in the fla1 is automatically deleted, the day after 2 years is complete.

SQL> create flashback archive fla1 tablespace tbs1 retention 2 year;  << data will be retained for 2 years.!

Flashback archive created.

2- Grant Flashback Archive objects privilege on Flashback data archive (fla1) to Archive User (SCOTT).

SQL> grant flashback archive on fla1 to Scott;

Grant succeeded

3- Enable history tracking for a table in the FLA1 archive:
ٍِAs Flashback Archive User (SCOTT)

SQL> alter table EMP flashback archive fla1;
Table altered.

4- View the historical data:
SQL> select to_timestamp(sysdate) from dual;

TO_TIMESTAMP(SYSDATE)
—————————————————————————
02-DEC-07 12.00.00 AM

SQL> alter table EMP flashback archive fla1;

Table altered.

SQL> update emp set sal=sal+100 where empno=7788;

1 row updated.

SQL> select sal from emp as of timestamp to_timestamp(‘2007-12-02 00:00:00′,’yyyy-mm-dd hh24:mi:ss’) 3 where empno=7788;

SAL
———-
3100

SQL> select sal from emp where empno=7788;

SAL
———-
3200

Note:

To use the flashback_archive_clause to disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINISTER system privilege or you must be logged in as SYSDBA.

Flashback archiving is handled by fbda background process, and it checks for tablespace quota every 1 hour. If you run out of flashback archival space and must extend the tablespace you may also need to reset the quota for the tablespace.

References

BUG:6901089 – FLASHBACK ARCHIVE CAN NOT BE LIMITED USING QUOTA.

 

 

 

 

ORA-959, ORA-942, ORA-1536 in FDBA tracefile [ID 856900.1]

  Modified 27-JUN-2011     Type PROBLEM     Status PUBLISHED  

In this Document
Symptoms
Cause
Solution


Applies to:

Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.2.0.2 – Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

Background process FBDA tracefile shows errors like :

Flashback Archive: Error ORA-959 in SQL create table SCOTT.SYS_FBA_DDL_COLMAP_17401 (STARTSCN NUMBER, ENDSCN NUMBER, XID RAW(8), OPERATION VARCHAR2(1), COLUMN_NAME VARCHAR2(255), TYPE VARCHAR2(255), HISTORICAL_COLUMN_NAME VARCHAR2(255)) SEGMENT CREATION IMMEDIATE tablespace FLA
Flashback Archive: Error ORA-942 in SQL insert into SCOTT.SYS_FBA_DDL_COLMAP_17401 (ENDSCN, COLUMN_NAME, TYPE, HISTORICAL_COLUMN_NAME)values (NULL, ‘EMPNO’, ‘NUMBER(4)’, ‘EMPNO’)
Flashback Archive: Error ORA-942 in SQL “select count(*) from SCOTT.SYS_FBA_DDL_COLMAP_17401”
Flashback Archive: Error ORA-942 in SQL “select count(*) from SCOTT.SYS_FBA_DDL_COLMAP_17401”
Or errors like :

Flashback Archive: Error ORA-1536 in SQL create table SCOTT.SYS_FBA_DDL_COLMAP_17401 (STARTSCN NUMBER, ENDSCN NUMBER, XID RAW(8), OPERATION VARCHAR2(1), COLUMN_NAME VARCHAR2(255), TYPE VARCHAR2(255), HISTORICAL_COLUMN_NAME VARCHAR2(255)) SEGMENT CREATION IMMEDIATE tablespace FLA

 

Cause

The tablespace for the FLASHBACK ARCHIVE does not exist or the related user does not have a quota on that tablespace;

Solution

Check if the tablespace exists :

SQL> select ‘tablespace : ‘ || tablespace_name || ‘ does not exist!!!!’
from dba_flashback_archive_ts
where tablespace_name not in (select tablespace_name from dba_tablespaces);
Check for quota of the owner of the table on the related tablespace :

Either have a quota on the tablespace or the UNLIMITED TABLESPACE privilege

SQL> select ta.*
from dba_flashback_archive_tables ta,
dba_flashback_archive_ts ts
where ta.flashback_archive_name = ts.flashback_archive_name
and ts.tablespace_name not in ( select tablespace_name
from dba_ts_quotas)
and ta.owner_name not in (select grantee
from dba_sys_privs
where privilege = ‘UNLIMITED TABLESPACE’
and grantee = ta.owner_name);

Rows returned by the above query are Flashback Archives which have a quota problem.

Example :

SQL> select ta.*
from dba_flashback_archive_tables ta,
dba_flashback_archive_ts ts
where ta.flashback_archive_name = ts.flashback_archive_name
and ts.tablespace_name not in ( select tablespace_name
from dba_ts_quotas)
and ta.owner_name not in (select grantee
from dba_sys_privs
where privilege = ‘UNLIMITED TABLESPACE’
and grantee = ta.owner_name);

TABLE_NAME OWNER_NAME
—————————— ——————————
FLASHBACK_ARCHIVE_NAME
——————————————————————————–
ARCHIVE_TABLE_NAME STATUS
—————————————————– ——–
EMP SCOTT
FLA1
SYS_FBA_HIST_17401 ENABLED

Author: admin