What is Oracle GoldenGate?

I was looking for a short & crisp answer to the question what is Oracle Golden Gate which could be called a defination of the product as well.

Oracle GoldenGate replication technology that’s now part of the Oracle framework, is a high-performance software application for real-time transactional change data capture, transformation, and delivery, offering log-based bidirectional data replication. The application enables you to ensure that your critical systems are operational 24/7, and the associated data is distributed across the enterprise to optimize decision-making.
Oracle GoldenGate filled a gap which we did not have - heterogeneous replication, replication from Oracle to different databases and vice versa.Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.One can move data between similar or dissimilar supported Oracle versions, or one can move data between an Oracle database and a database of another type. GoldenGate supports the filtering, mapping, and transformation of data.

A quick checklist for Oracle upgrade 9i To 10gR2

1. Take a consistent backup of your database.
2. Install Oracle 10gR2
3. Check objects status in the db.
Compile invalid objects.
@?/rdbms/admin/ utlrp.sql
4. Create SYSAUX tablespace.
A manadatory requirement for Oracle 10G
5. Run utlu102i.sql script which checks the db readiness to upgrade to 10g.
This script will be available in oracle10g home. So specify complete path for oracle 10g rdbms/admin directory
6. Shutdown immediate the database.
7. Copy the parameter file & password file from existing home to new Oracle 10g Home.
8. Edit oratab and rerun oraenv to bring the changes in affect.
Oratab would be in /etc/oratab OR /var/opt/oracle/oratab
9. Startup the database in upgrade mode.
Command: startup upgrade
10. Now upgrade for which run catupgrd script. Spool the output.
This can take upto 40 mins to complete.
@?/rdbms/admin/catupgrd.sql
11. Now recompile invalid objects.
Run utlrp.sql
Remember to compare the status of objects with the one before upgrade. Current should be same or Less.
12. Check the status of upgrade
@?/rdbms/admin/utlu102s.sql
13. Alter / reset the parameter file and set the compatibility parameter.

The upgrade of Oracle 9i To 10g is completed successfully.

For further detailed reading refer Oracle documentation & metalink docs.
Useful read about Optimizer stats while upgrading:
http://optimizermagic.blogspot.com/2008/02/upgrading-from-oracle-database-9i-to.html

Metalink Doc: Complete Checklist for Manual Upgrades to 10gR2 [ID 316889.1]

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.

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

Oracle data guard – new additions in 10R2

Oracle Data Guard has improved functionality over last releases, which is hitting the point home that Oracle is serious about it’s view of making administration so easy that a child can handle it.

Hmm….. Mediocre DBA’s better start looking for another professional line.

In 10g release 2, Oracle data guard broker new features are:

·         Fast-Start Failover

DG broker can automatically fail over to a previously chosen standby database in the event of a loss of primary database. This would not require any manual steps. Moreover Oracle 10gR2 claims that the former primary database is automatically re-instated as a standby database in the new broker configuration when a connection to it is re-established.

I would test this rigorsly in my environment. Database is never a standalone in any production environment and lots of other connections and application requirements would be involved in case of an automatic failover. I would seek answers more towards the applications and environment requirements before using this feature.

·         Re-instatement of the primary database to a standby database after a failover

Oracle claims that the data guard observer can reinstate the former primary database as a standby database. Oracle documentation states “Reinstatement restores high availability to the broker configuration so that, in the event of a failure of the new primary database, another fast-start failover can occur….”

Currently, this feature has too many limitations making it highly unusable in actual production environment. In Oracle 11g this feature seems to have enhanced.

·         Data Guard enhancements in OEM grid control

Oracle 10g Data Guard broker features are only available in Enterprise Manager grid Control. So, one must install OEM grid control to ba able to access the data guard functionality.

Ø  Compression of backups during standby creation

Ø  Support for flash recovery area for physical standby databases

Ø  Support of standby databases in an Oracle Managed Files (OMF) or Automatic

Storage Management (ASM) configuration

Ø  New and improved apply statistics

Ø  Status alerts for non-broker configurations

Ø  Test redo generator

Oracle dynamic view -- more than 20 mins data lost!!

An interesting question was asked in an interview for DBA and the person started this discussion about V$ views as they are popularly called.

I still prefer to call them by their actual name i.e. Oracle’s dynamic performance views. The behavior and the data expected gets more clear through it's name. These views ONLY have data or information about the instance and hence on reboot or restart of an instance the data would be refreshed and old information would be lost.

The question was rephrased in an interview and asked as a scenario... " In oracle database the information in oracle dynamic view (not dba_ view) more than 20 minutes got lost, what is the reason to result in this? How do you get the information in dynamic view more than 20 minutes?"

I saw people give the answer as recover the database to to the point in time required OR refresh the view, totally way off the mark!!

The dynamic performance views show statistics for the entire instance since the time it was started. So, if the info older than 20 mins is gone, would it would mean that the instance had been restarted 20 mins earlier. 

Dynamic performance views have only the current instance statistics. and recovering a database from backup is not going to recover this statistics. Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice, even between patch releases. Application developers should be aware of this and write their code to tolerate missing or extra statistics.

Oracle documentation states...

 "V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since the instance was started."