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.

Case Sensitive Password

Passwords have become case sensitive from Oracle 11g onwards.
In Earlier releases password was not case sensitive.
The case sensitive feature is default feature for Oracle 11g databases. Of course this feature can be enabled/disabled with an initialization parameter SEC_CASE_SENSITIVE_LOGON

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.

The case sensitive password functionality can be seen below.

SEC_CASE_SENSITIVE_LOGON initialization parameter is TRUE and creates a new user with a mixed case password.
CONN / AS SYSDBA
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> CREATE USER testuser IDENTIFIED BY TestUser;
SQL> GRANT CONNECT TO testuser;

2. Trying to connect using different case passwords.

SQL> CONN testuser/TestUser
Connected.
SQL> CONN testuser/testuser
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>

3. Changing the parameter SEC_CASE_SENSITIVE_LOGON to FALSE and we can connect.

CONN / AS SYSDBA
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
SQL> CONN testuser/TestUser
Connected.
SQL> CONN testuser/TESTUSER
Connected.
SQL>

An important point is that even when case sensitive passwords are not enabled, the original case of the password when it was created/modified is retained. Which means that the passwords case sensitivity can be used in subsequent settting of the parameter SEC_CASE_SENSITIVE_LOGON to TRUE