How the Archived Redo Log Deletion Policy works

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Note that *only* logs in the flash recovery area can be deleted automatically by the database.

You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.

When the Archived Redo Log Deletion Policy Is ENABLED

You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.

For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.

You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

When the Archived Redo Log Deletion Policy Is DISABLED

In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.

The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.


Source: Oracle Documentation

How does Oracle manage disk space in the Flash Recovery Area?

Files in the recovery area are permanent or transient.

Permanent files are active files used by the database instance (like control files).
All files that are not permanent are transient.

In general, Oracle Database eventually deletes transient files after they become obsolete under the backup retention policy or have been backed up to tape.

Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion.
Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose.
Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape.
When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.

The following rules govern when files become eligible for deletion from the recovery area:
• Permanent files are never eligible for deletion.
• Files that are obsolete under the retention policy are eligible for deletion.
• Transient files that have been copied to tape are eligible for deletion.
• Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements. Consumers of logs can include RMAN, standby databases, Oracle Streams databases, and the Flashback Database feature.

The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy and archived log deletion policy.
To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.

Source: Oracle Documentation

How to restore archive logs to a new destination

This example restores all archived redo logs to the /oracle/temp_restore directory:

RMAN> RUN
{ 
  SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore/'; <-- note the last /
  RESTORE ARCHIVELOG ALL;
}

If the file is already on disk you will get an error message from RMAN.

To override this, use the force option:

RMAN> run {
2> allocate channel t1 device type 'sbt';
3> set archivelog destination to '/oracle/temp_restore/';
4> restore archivelog logseq 15572 force;
5> }

How is the database time zone set?

Answer: it is set at creation time.
If not explicitly defined by the DBA, it will use the time zone of the server's operating system.

If you want to set it explicitly, do so in the CREATE DATABASE statement:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='-05:00';

Or set it to a named region, like this:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='Europe/Zurich';

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns.

You can change the database time zone by using the SET TIME_ZONE clause of the ALTER DATABASE statement:

ALTER DATABASE SET TIME_ZONE='05:00';
ALTER DATABASE SET TIME_ZONE='Europe/Zurich';

The ALTER DATABASE SET TIME_ZONE statement will return an error if the database contains a table using a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data. You will also have to restart the database.


To see the current time zone of the database:
SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;

DBTIMEZONE	SESSIONTIMEZONE
+01:00	Europe/Zurich

For a database used globally, it may be beneficial to set the database time to UTC (0:00) regardless of where it is physically hosted.

Source: Oracle Documentation

How does Oracle Data Pump Import handles referential integrety constraint violations?

How does Oracle Data Pump Import handles referential integrety constraint violations?

To find out, let's use the two tables SHIPS and PORTS, which are connected by a referential constraint from SHIPS to PORTS, called FK_SHIPS_PORTS.

CREATE TABLE SHIPS
(
  SHIP_ID       NUMBER,
  SHIP_NAME     VARCHAR2(20 BYTE),
  CAPACITY      NUMBER,
  LENGTH        NUMBER,
  HOME_PORT_ID  NUMBER,
  LIFEBOATS     NUMBER(3),
  CONSTRAINT PK_SHIPS PRIMARY KEY (SHIP_ID),
  CONSTRAINT FK_SHIPS_PORTS FOREIGN KEY (HOME_PORT_ID) 
  REFERENCES PORTS (PORT_ID)
);

CREATE TABLE PORTS
(
  PORT_ID    NUMBER,
  PORT_NAME  VARCHAR2(20 BYTE),
  COUNTRY    VARCHAR2(40 BYTE),
  CAPACITY   NUMBER,
  CONSTRAINT PK_PORTS PRIMARY KEY (PORT_ID)
);

In other words, you cannot add a ship without a valid PORT_ID that already exist in table PORTS.

PORT_ID	PORT_NAME	COUNTRY	CAPACITY
1	Baltimore	USA
2	Charleston	USA
3	Tampa	USA
4	Miami	USA

SHIP_ID	SHIP_NAME	CAPACITY	LENGTH	HOME_PORT_ID	LIFEBOATS
1	Codd Crystal	2052	855	1	80
15	Codd Champion	2000	650		30
2	Codd Elegance	2974	952	2	95
16	Codd Victorious	2055	876	2
17	Codd Grandeur	2030	840	4
18	Codd Prince	1500	550	2	32
20	Codd Norway	1500	900	3	80

I now remove one row from the PORTS table, so that new rows being imported will have a missing parent key. In order to do that I need to also remove any child record from SHIPS, in my case, only one:

DELETE TESTUSER.SHIPS WHERE HOME_PORT_ID = 1;
DELETE TESTUSER.PORTS WHERE PORT_ID = 1;
COMMIT;

1 row deleted.
1 row deleted.
Commit complete.

SCENARIO 1: "What happens if we use TABLE_EXISTS_ACTION=REPLACE when the table being replaced has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• All rows are imported into SHIPS, including the offending ones
• The referential constraint is dropped.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (TESTUSER.FK_SHIPS_PORTS) - parent keys not found
Failing sql is:
ALTER TABLE "TESTUSER"."SHIPS" ADD CONSTRAINT "FK_SHIPS_PORTS" FOREIGN KEY ("HOME_PORT_ID") REFERENCES "TESTUSER"."PORTS" ("PORT_ID") DEFERRABLE ENABLE


SCENARIO 2: "What happens if we use TABLE_EXISTS_ACTION=TRUNCATE when the table being truncated has a referential constraint to another table?"

Result:

• The operation will report an error because of a constraint violation issue.
• The SHIPS table is left truncated, no rows are loaded.
• The referential constraint stays.

Error message:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTUSER"."SHIPS" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (TESTUSER.FK_SHIPS_PORTS) violated - parent key not found


Both cases requires manual intervention afterwards to cleanup offending rows, and then to recreate the constraint.
One way to do so would be to allow for orphan rows through temporarily disabling the index, loading the rows, then reenabling the index using the NOVALIDATE option.

First disable the constraint:
SQL> ALTER TABLE SHIPS DISABLE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

Load the data once more:
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "TESTUSER"."SHIPS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."SHIPS" 7.289 KB 7 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Finally, enable the constraint again. However, tell oracle to disregard existing rows, only apply the constraint to new rows:
SQL> ALTER TABLE SHIPS ENABLE NOVALIDATE CONSTRAINT FK_SHIPS_PORTS;

Table altered.

SQL>

Example is based on Steve O'Heam's "SQL Certified SQL Expert Exam Guide"

How to drop a table partition without invalidating global indexes

My interval range partitioned table looks as follows:

CREATE TABLE SEGMENT_SIZES(
  SEGMENT_SIZES_ID NUMBER,
  STIMESTAMP      DATE,
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(30 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(20 BYTE),
  BYTES           NUMBER,
  CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;

It was populated with data, which created the needed partititions automatically.
The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;

However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;

Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!

Source: Oracle Documentation

why am I getting ORA-14006: invalid partition name when attempting to drop a partition in TOAD?

In TOAD you are trying to drop a partition, as follows:

alter table segment_sizes drop partition SYS_P41;

But you get ORA-14006: invalid partition name as a result.

Solution: remove the ";" at the end of the statement, and try again.
Alternatively, execute the statement through SQL*plus.

Source: Derya Oktay's Oracle Weblog