Home » Server Options » Streams & AQ » Will Suppliment logging finish
Will Suppliment logging finish [message #255566] Wed, 01 August 2007 04:07 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I issued following command from SQL*PLUS
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

in order to specify suppliment logging to configure Oracle Streams replication. But it passed 5 hours after issuing. Will this altering finish? When? How I can monitor how much it finished of its task.

Is really logging suppliment data take a significant time.
Re: Will Suppliment logging finish [message #255583 is a reply to message #255566] Wed, 01 August 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It should not.
See v$lock if session is not waiting on a lock.
Otherwise activate a 10046 trace level 8 on the session executing the statement to see what it is waiting for.

Regards
Michel
Re: Will Suppliment logging finish [message #255601 is a reply to message #255566] Wed, 01 August 2007 04:47 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select sid , username, status from v$session where username='STRMADMIN';

       SID USERNAME                       STATUS
---------- ------------------------------ --------
       288 STRMADMIN                      ACTIVE
       302 STRMADMIN                      INACTIVE

SQL> select sid from v$lock where sid in (288,302);

       SID
----------
       302


SQL>  select sid,wait_time,state,lockwait,seconds_in_wait from v$session where username='STRMADMIN';

       SID  WAIT_TIME STATE               LOCKWAIT         SECONDS_IN_WAIT
---------- ---------- ------------------- ---------------- ---------------
       288         -1 WAITED SHORT TIME                                  0
       302          0 WAITING                                          930

SQL>  select sid, request,block,type from v$lock where sid=302;

       SID    REQUEST      BLOCK TY
---------- ---------- ---------- --
       302          0          0 TX




302 is in v$lock. What to do?Michel keep in contact.

[Updated on: Wed, 01 August 2007 04:58]

Report message to a moderator

Re: Will Suppliment logging finish [message #255610 is a reply to message #255601] Wed, 01 August 2007 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from v$session_wait where sid=302;

Regards
Michel
Re: Will Suppliment logging finish [message #255611 is a reply to message #255566] Wed, 01 August 2007 05:16 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL>  select * from v$session_wait where sid=302;

       SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW            P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- -------------------
       302        184 SQL*Net message from client                                      driver id                                                        1650815232 0000000062657100 #bytes                                                                    1 0000000000000001                                                                           0 00                  2723168908           6 Idle             
Re: Will Suppliment logging finish [message #255618 is a reply to message #255611] Wed, 01 August 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The session is waiting for you to enter a command, so your statement is completed.

Regards
Michel
Re: Will Suppliment logging finish [message #255621 is a reply to message #255566] Wed, 01 August 2007 05:48 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

If the statement completed then supplimental logging of primary key , foreign key and unique key will be enabled. Now Whenever I try
SQL> select SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_PK from v$database;

SUP SUP SUP
--- --- ---
NO  NO  NO


What to do now?
Re: Will Suppliment logging finish [message #255625 is a reply to message #255621] Wed, 01 August 2007 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I tried and got:
SQL> select SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_PK from v$database;
SUP SUP SUP
--- --- ---
NO  NO  NO

1 row selected.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  2    (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_PK from v$database;
SUP SUP SUP
--- --- ---
YES YES YES

1 row selected.

So I can't say more than "retry".

Regards
Michel
Re: Will Suppliment logging finish [message #255628 is a reply to message #255566] Wed, 01 August 2007 06:03 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Oh Michel this Supplemental logging really finished me .

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  2  (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-32593: database supplemental logging attributes in flux


How long time I should wait?


I performed in another database it took almost milisecond.But the database in which it was successfully finished in that there is less number of tables and no user is currently logged on except me.

What I should do now?.

[Updated on: Wed, 01 August 2007 06:10]

Report message to a moderator

Re: Will Suppliment logging finish [message #255638 is a reply to message #255628] Wed, 01 August 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Switch log file, drop supplemental logging, switch logfile and retry.

Regards
Michel
Re: Will Suppliment logging finish [message #255817 is a reply to message #255566] Wed, 01 August 2007 22:03 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> alter system switch logfile;
System altered.

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS
*
ERROR at line 1:
ORA-32593: database supplemental logging attributes in flux


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS
*
ERROR at line 1:
ORA-32593: database supplemental logging attributes in flux


Michel I performed with same data in another machine successfully. But in this database how this can be achieved?
Re: Will Suppliment logging finish [message #255818 is a reply to message #255566] Wed, 01 August 2007 22:19 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I performed everything but I failed. But no still I got succeed. You know better than me what bring me success. Simply,

shutdown immediate
startup
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

Cool Cool Cool
Now everything works fine.
Re: Will Suppliment logging finish [message #255836 is a reply to message #255818] Thu, 02 August 2007 00:49 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The simplest solution is often the best one. Laughing

Regards
Michel
Previous Topic: Heterogenous Replication
Next Topic: Streams Replication Working or Not?
Goto Forum:
  


Current Time: Thu Mar 28 06:36:35 CDT 2024