Oracle Database Discussions

We would like to copy data from a 600 GB Oracle database (9i) to a separate database for reporting. We want to use Data Guard in Logical Standby mode. The source database is heavily used and we can't afford a significant increase in system load (e.g. I/O activity) on that system.

To set up a Logical Standby, we need to put the JD Edwards database into Supplemental Logging mode. I am concerned that this will noticibly increase the load on the source server.

Has anyone analyzed the additional overhead of Supplemental Logging?

I have done some testing using Oracle 10.2 (Oracle XE on my computer) which indicates that when I turn on Supplemental Logging, the size of the archive logs grows by 40%. I have not yet tested this on our 9i database.

Thank you in advance for your help!

Best Regards,

Mike

=================================

The code below demonstrates the symptoms mentioned above:

RESULTS - size of archive logs generated:

- With Supplemental Logging: 120 MB
- Without: 80 MB

=================================

CREATE TABLE "EMP"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0)
)
/

alter table emp add CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
/

CREATE TABLE "STAT"
( "F1" NUMBER,
"ID" VARCHAR2(10)
)
/

/******************************************************
The "employee" table is from Oracle XE samples
The procedure below generates transactions to test archive log size.

To run, put the database in archive log mode. Then pop an archive log by executing
ALTER SYSTEM ARCHIVE LOG CURRENT;
To flip between running with Supplemental Logging, use one of:
ALTER DATABASE drop SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
*******************************************************/
declare
i number;
begin
i := 0;
while i < 1000
loop
delete from emp;
insert into emp select * from employees;
update emp set COMMISSION_PCT = COMMISSION_PCT * .5;
update stat set f1 = i where id = 'UPD';
commit;
if i mod 1000 = 0 then
dbms_output.put_line(i);
end if;
i := i + 1;
end loop;
end;

/***********************************************/