Emre Baransel - Oracle Blog

Flashback on a Physical Standby Database

First topic is about using flashback feature on a physical standby database.
Metalink note "How To Open Physical Standby For Read Write Testing and Flashback" (805438.1) is one of the helpful sources. Another useful doc is "Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)" with the section "Using a Physical Standby Database for Read/Write Testing and Reporting" . And the last one comes from Alejandro Vargas : "Using a 10g R2 Oracle Physical Standby Database For Read/Write Testing and Reporting"

What they said :
"it's basically the manual process of doing what oracle's "snapshot standby" feature does in 11g."
"Just be sure to drop the restore point when you're done, or Oracle will fill up your FRA with flashback logs."

Discussion in Oracle-l group (freelists.org).

---------------------

SAN Level Replication vs. Dataguard?

Discussion in Oracle-l group (freelists.org) with "Replicating Live Oracle DataFiles/LUNs to remote site via SAN tool?" topic, has a nice discussion about comparison of SAN replication and Dataguard.



What they said:
"Replicating our DW db at the moment using EMC's Clarion synchronous replication over a dark fibre network to a DR site. 1.5TB size. The major determinant for it working is to have ALL luns for your db in one single synchronous replication group. More than one group and you'll have a corrupt replicated db. Synchronous SAN replication involves a performance penalty on ALL writes, not just data/index writes. Ie: temp and undo, all get written to both sites. That means: wide pipes needed. Ours does 400MB/s aggregate total and is *just* enough at the moment. "

"replication at a storage array level pros:
- Replicate anything - replicate not just your datafiles, but your oracle_home, your scripts, your backup locations, your third-party agents, everything. When I was at EMC, I even had a customer replicate their entire datacenter by running diskless servers booting off of a Symmetrix where everything was replicated to a remote datacenter
- Storage admins problem - the DBAs don't have to worry about care, feeding, housekeeping, the storage team manages it
- Very time-tested and reliable - even outside of SRDF, storage based replication technologies have all been proven to work time and time again

Storage level replication cons:
- Like swinging a very big hammer - it's a heavy tool, not a finesse solution - you get very few controls over what happens, limited number of supported configs, it can be bandwidth inefficient, and can require a lot of infrastructure
- Often requires dedicated infrastructure - dedicated WAN links, SAN switches, DWDM, etc. etc."

" SAN Level Replication Pros:
1) For you it's extremely easy to set up. You just sit back and watch the Unix admin do all of the work. I like that.
2) If there is a problem with the solution your not on the carpet for it.
3) If and when you upgrade of patch the remote system gets the update as well, less work. Assuming that ORACLE_HOME is replicated as well.

SAN Level Replication Cons:
1) If the DR database doesn't start for any reason you know who's to blame. You of course.
2) If your database expands onto new luns they may or may not be included in the replication works.
3) Adding a new lun in some products requires downtime because you have to rebuild the remote system.
4) You need a LARGE network pipe between the sites and it HAS to be reliable.
5) If you do have a network issue between the sites your database can hang because the replication software is bogged down. Not likely to occur immediately or in the event of a short outage, but longer outages will get there sooner or later."

"With storage level replication, corruptions coming from Oracle/OS software or host/HBA hardware get replicated to DR site. With Data Guard, many corruptions in redo stream would be detected and not propagated to DR database, thanks to checksums and sanity checks.
Also, human errors, such accidential datafile deletion, overwriting or formatting wrong device etc get replicated without a question and ability to roll it back.
In my mind the biggest benefit of storage level replication would be that you can offload most of your replication to OS/Storage team, without having to come up with different strategy for each database/application in house.
But for Oracle purposes I prefer data guard due all the extra flexibility."

"If you are looking for solution for a few databases then maybe you should go with data guard. If you are looking for enterprise solution, you should go with storage level replication."

---------------------

SQLBLANKLINES

This is an SQLPLUS topic: SQLBLANKLINES

Question is "In my SQL*Plus session, if I press Enter twice, it goes back to the prompt. How do I change this kind of behavior?"

Answer is using SQLBLANKLINES as in the following example:

optionsprodban:SQL> select sysdate
2
prodban:SQL> from dual;
SP2-0042: unknown command "from dual" - rest of line ignored.
prodban:SQL> set sqlblanklines on
prodban:SQL> select sysdate
2
3 from dual;SYSDATE
---------
24-AUG-09prodban:SQL>
HTH,Lyall

Discussion in Oracle-l group (freelists.org).

---------------------

Become Another User in SQLPlus

What i was searching is becoming another user without password with SYS user, and the answer came from Tom Kyte:
How to become another User in SQLPlus (su.sql script)

Let's look what this script looks like:

whenever sqlerror exit
column password new_value pw
declare
l_passwd varchar2(45);
begin
select password into l_passwd
from sys.dba_users
where username = upper('&1');
end;
/
select password
from sys.dba_users
where username = upper( '&1' )
/
alter user &1 identified by Hello;
connect &1/hello
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue