Update: Legacy Mode
In 11.2, Oracle introduced ‘legacy mode‘ options for expdp and impdp.
One now can use the old exp and imp parameters, including ‘CONSISTENT=Y’.
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Parameter File, Replaced with:
"flashback_time=TO_TIMESTAMP('2013-05-07 07:18:29', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode has set reuse_dumpfiles=true parameter.
For 11.1 and older, original post:
Last few weeks I have been exporting 10g databases for migrating them to 11g. I knew Export Data Pump was read consistent (I have been running into ORA-01555‘s), but I was surprised to see a ORA-02298 (parent keys not found) when importing:
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (SCOTT.FK_SCALE_ID) - parent keys not found
Failing sql is:
ALTER TABLE "SCOTT"."SCALE_ID" ADD CONSTRAINT "FK_SCALE_ID" FOREIGN KEY ("SCALE_ID") REFERENCES "SCOTT"."SCALE" ("ID") ENABLE
I thought the expdp had made an consistent database dump, but didn’t! The FK’s present in SALARY were not present as PK’s in the SCALE table, so this could only mean the dump was not database consistent at all!
Solution: Using the flashback_time option
The Easy Connect Naming Method allows you to connect to a database service without having to configure Net Service Names.
The most found example on internet is the one with typing the password at the command prompt when starting sqlplus:
$> sqlplus system/manager@db_machine:1521/db_servicename
However, this means that the password will be stored in you history of commands (linux). This is not really secure… but sqlplus does not accept it when no password is supplied when using easy connect.
If you want to login with easy connect without typing the password on the command line, you have two options: Continue reading
When using the Fast Recovery Area for storing archive logs, one probably wants to use this location to make use of the self-managing deletion rules of files.
One of these rules is that files are obsolete under the retention policy are eligible for deletion. When for instance RMAN needs space for backup (without delete obsolete option), Oracle will do a similar ‘delete obsolete’ command to make space in the Fast Recovery Area.
For archived logs to be eligible, one MUST use the USE_DB_RECOVERY_FILE_DEST setting for log_archive_dest_x. If the actual location is used, Oracle does not include archived logs in the self-managing deletion rules of files!!! Continue reading
When you want to delete an ASM diskgroup, you might encounter the following error:
ORA-15027: active use of diskgroup “DATA” precludes its dismount Continue reading
I was preparing some fresh installed OEL5.5 machines for database software installation, but after running ‘yum install oracle-validated‘, the OUI did not pass the prerequisites on two packages. It seems it did not install unixODBC-devel x86_64 and libaio-devel x86_64.
Their Linux may be unbreakable, but their ‘oracle-validated’ package on the public yum server seemed to be broken. Continue reading
Many people will use the WebLogic’s NodeManager to use the start script for starting Managed Servers:
The NodeManager will start the Managed Server using the script and monitor the Managed Server when it fails.
One of the mistakes people can make is creating their own start script and start the Managed Server using ‘nohup &’ within this script: Metalink [ID 984122.1] and [ID 861098.1]. The script will finish and the server keeps running, but the NodeManager thinks the Managed Server has failed… Continue reading
Oracle has released it’s first patch set for Oracle Database 11g Release 2 (184.108.40.206).
See the README at Oracle® Database Patch Set Notes 11g Release 2 (220.127.116.11) Patch Set 1 and Metalink Note ID 1189783.1 at Important Changes to Oracle Database Patch Sets Starting With 18.104.22.168 for more information.
— Checked for relevance on Januari 21, 2017
The Oracle Database version compatibility matrix and version support status matrix are almost mandatory when installing or upgrading clients or servers.
For information about patches, go here: Oracle database patches for Linux and Windows (on Metalink).
For announcements of security fixes, go to Critical Patch Updates, Security Alerts and Third Party Bulletin.
Oracle Database client / server version compatibility matrix
Oracle calls this Client / Server Interoperability Support, Note ID 207303.1
Oracle Database version release support status matrix
Oracle calls this the Database Releases Support Status Summary, Note ID 161818.1
You will be redirected to Oracle Support and you must be registered to gain access to the pages. I did not include the matrices, because these change over time.
Oracle Information about Windows 32 / 64 bit database and client versions
Certification Information for Oracle Database on Microsoft Windows x64 (64-bit), Doc ID 1307195.1
I knew in Oracle 11g with Automatic Memory Management (AMM), the database was able to give unused memory back to the OS, controlled by MEMORY_TARGET and MEMORY_MAX_TARGET.
This mechanism is present in 11g since day 1 (in 2007), but I never actually played with it.
MEMORY_TARGET and MEMORY_MAX_TARGET
The Oracle documents state the following:
MEMORY_TARGET specifies the Oracle system-wide usable memory.
MEMORY_MAX_TARGET (…) decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future.
So my guess is, MEMORY_MAX_TARGET (static) is the maximum you can set MEMORY_TARGET (dynamic) to. A couple of days ago, I wanted to experiment a bit with these memory settings.
In this blog post I will talk about the basic workings of Automatic Undo Management, which can cause ORA-01555 and ORA-30036 issues.
The scope is Automatic Undo Management used in 10g and 11g, but has to be explicitly set for 9i (UNDO_MANAGEMENT = AUTO). Manual Undo Management is out of scope for this blog.
The Undo tablespace is a normal tablespace like any other, but only Oracle is controlling what is happening inside it.
The Undo tablespace is used for several features: ROLLBACK, READ CONSISTENCY and FLASHBACK technology. Continue reading