Basic Properties of a Database Transaction ( Part 2)

Oracle enforces ACID by means of Undo Segments & Redo Logs.
Undo Segments help enforce-- atomicity and consistency .
Isolation requires undo segments & locks.
Durability is enforced with redo logs.

Oracle provides the following transaction isolation levels.
Read committed
Default transaction level is Read Committed.
Each query executed will only see the committed data. In other words an Oracle query will never read uncommitted data.
Serializable
Serializable transactions can only see those changes that were committed at the time the transaction began and those changes that are being made by the transaction itself.
Read-only
Read-only transactions see only those changes that were committed at the time the transaction began

Isolation levels can be set at the begining of the transaction and at session level.
Commands for transaction level setting of isolation.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;

Commands for session level setting;
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

Basic Properties of a database Transaction (Part 1)

Basic properties of any databse transaction should be Atomicity, Consistency, Isolation, and Durability.
In short referred to as ACID.
All Oracle database transactions are ACID complaint . However, I believe that Oracle's Berkeley DB database is not ACID-compliant.
I need to research more on this statement though.
In short ACID refers to:
Atomicity
The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.
Consistency
The transaction takes the resources from one consistent state to another.
Isolation
A transaction's effect is not visible to other transactions until the transaction is committed.
Durability
Changes made by the committed transaction are permanent and must survive system failure.

Migrating Oracle db from 32bit to 64bit

Database: Oracle 10gR2

Migrating or upgrading the database bitsize can be at times as challenging as upgrading the db version.
I have here a quick steps list to guide through this process.
The bitsize of the Database file is to be increased. So, obviously, we have the 64bit software installed / binaries placed in the OS which is 64bit as well. More addressable size would require more memory as well. So, the SGA & various pools sizes to be doubled. This doubling of basic memory parameters calculation works well! I have read this in one of the Oracle support notes though I am not sure which one.
Steps:
1. Update the following parameters
Job_queue_processes=0
_system_trig_enabled=False
Aq_tm_processes=0
2. Double the SGA & Pools parameters values (eg: sga_max_size,java_pool_size..)
3. Now with all the files in same location as the 32bit server, you can follow below:
Startup upgrade
4. Execute file @?\rdbms\admin\utlu102i.sql
5. Check for any errors. Troubleshoot accordingly.
In case error:

ORA-06553: PLS-801: internal error [56319]

and no other message/error seen, then first check all the pool parameters if values in ok
6. Shutdown immediate;
7. Startup
8. @?\rdbms\admin\utlirp.sql
9. Shutdown immediate
10. Now comment out / remove two parameters from pfile
aq_tm_processes
_system_trig_enabled
11. Startup
12. Check all required components are valid
Select comp_name,status,substr(version,1,10) as version from dba_registry;

If all Valid... Migration is completed.
now update job_queue_processes to the value required.

Interestingly, I have migrated more than 10 databases bitsizes. First couple of them were cheese maybe since Olap was not installed.
The ora-600 I found in my next migration gave me a big run for my job since, the given downtime was reaching towards it's end.
So, If you get below error then be assured it is olap.

ORA-00600: internal error code, arguments: [XSOOPS], [xsOBJTYPE1], [], [], [], [], [], []

which I am sure must have been found as invalid while checking the components in pt 12 as well.
In such case, unistall Olap & reinstall.

To uninstall Olap run following scripts

SQL> @?/olap/admin/catnoamd.sql
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/catnoxoq.sql

To reinstall Olap

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

Reference material: Oracle notes:Remove Invalid OLAP Objects [ID 565773.1]
How To Remove or To Reinstall the OLAP Option To 10g and 11g [ID 332351.1]

EMD upload error:

Oracle agent can throw an EMD error for disk full.
Basically it means that the Disk on which agent is showing used percentage more than 98%

Error Seen

EMD upload error: Upload was successful but collections currently disabled - disk full 

Reason for the above error is that the EMD disk system shows used percent more than 98%.The agent requires the space for upload files is 98% by default. The agent collections will stop when the space on the disk is used beyond the default.
Solution is to release the space on the disk.
Also update the parameter UploadMaxDiscUsedPct=99 & UploadMaxDiskUsedPctFloor=99 in the emd.properties file

./emctl stop agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
The Oracleagent10gAgent service is stopping.....
The Oracleagent10gAgent service was stopped successfully.

./emctl start agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
The Oracleagent10gAgent service is starting..............
The Oracleagent10gAgent service was started successfully.

./emctl upload agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Drop database; SQL*Plus command

This command was introduced in Oracle10g and is very efficient.
The command will clean up all datafiles, online redo log files, controlfiles and spfile. It will not touch pfile & password file. On windows server, the service need to be deleted manually.
In case, one wants to remove archivelogs & backups execute this command from RMAN using the 'Including backups' option.
Word of caution here:::All RMAN backups associated with target database will be deleted from all configured device types.
Steps:

shutdown abort;  
startup mount exclusive restrict;   
drop database;  

Oracle mentions in it's document... that user must have SYSDBA system privilege to issue this statement. The database must be mounted in exclusive and restricted mode, and it must be closed.

Link::

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_8009.htm#i215798

Logical Standby SQL apply not re-starting

Errors ORA-00604 & ORA-01425 while restarting SQL apply for logical standby.
When one starts the SQL apply on the database...

SQL>Alter database start logical standby apply immediate;  
Database altered 

Oracle starts the SQL apply but subsequently it fails. In the alert log one would find the following errors..

Errors detected in process 16, role LOGICAL STANDBY COORDINATOR.  
krvsqn2s: unhandled failure 604:  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  

There would be an associated trace file, which would show...

*** SERVICE NAME:(SYS$BACKGROUND) 2011-03-08 12:21:04.572  
*** SESSION ID:(632.180) 2011-03-08 12:21:04.572  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  
knahcapplymain: encountered error=604  
*** 2011-03-08 12:21:04.619  
ksedmp: internal or fatal error  
ORA-00604: error occurred at recursive SQL level 1  
ORA-01425: escape character must be character string of length 1  

This is due to a Bug: 5108158 which is fixed in Oracle11g. Well!! I have heard that this is when we set logical standby to skip some schemas. Anyhow, this was not the case in our standby database neither could I find this skip schemas reason documented by Oracle !! :)
There is a workaround which would get the SQL apply to re-start. The workaround is documented in metalink 748208.1

SQL>--Ensure SQL apply is stopped  
SQL> Alter database stop logical standby apply;  
SQL>set echo on   
SQL>set pagesize 100   
SQL>spool workaround.log   
SQL>select * from system.logstdby$skip;   
SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip;   
SQL>select * from system.logstdby$skip where esc is null;   
SQL>update system.logstdby$skip set esc = '\'  where esc is NULL;  
-- Following should return no rows (due to update above)   
SQL>select * from system.logstdby$skip where esc is null;   
-- should no longer see any NULL in output   
SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip;   
-- Capture a snapshot of the final results   
SQL>select * from system.logstdby$skip;   
-- commit changes   
SQL>commit;   
--Restart the SQL apply and check the alert log.  
SQL> Alter database start logical standby apply immediate;  
SQL>Spool Off;  

In case, still SQL apply does not start, then immediately contact oracle support.

Performance statistics of database

Interestingly, maybe not surprisingly, we tend to let certain things go in background since, we are not using/doing that action as part of our daily work .I am grateful to Syed who pushed me to move from my comfort zone.
Preserving performance data in different Oracle versions...
Oracle 9i=> Performance statistics of a database was generally created in perfstat schema. So all we had to do was export user perfstat data and this dumpfile could be used for importing in another database in case required for analysis.
Oracle 10g=> introduced a few new reports and performance statistics was by default enabled to take snapshots every hour. Automatic Workload Repository (AWR) for cumulative and delta values at all levels except session & active session history (ASH) for the current state of all active sessions. By default we have snapshots of the performance data once every hour and the statistics are retained in the workload repository for 7 days.
To preserve this performance data, we gotta use "awrextr.sql" script to extract data into a data pump export file and "awrload.sql" script to load this data from dump file.
Oracle 11g=> The same system holds. A few enhancements are of course seen. So, to preserve this Oracle database performance data, we gotta use "awrextr.sql" script to extract data into a data pump export file and "awrload.sql" script to load this data from dump file.