Home » RDBMS Server » Server Administration » How to obtain DDL for REDO & UNDO? (RH ES4 Oracle V10.2.01)
How to obtain DDL for REDO & UNDO? [message #325342] Thu, 05 June 2008 18:37 Go to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I've used DBMS_METADATA.GET_DDL frequently to obtain DDL for various database objects.

However, it appears that neither REDO nor UNDO are considered DB objects.
  1* select distinct object_type from dba_objects order by 1
SQL> /

OBJECT_TYPE
---------------------------------------------------------
CLUSTER
CONSUMER GROUP
CONTEXT
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

40 rows selected.


How does one reverse engineer DDL for UNDO and/or REDO?
I am sure it can be done via SQL, and hopefully somebody has already done so & I can avoid reinventing this wheel.

TIA
Re: How to obtain DDL for REDO & UNDO? [message #325393 is a reply to message #325342] Fri, 06 June 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For UNDO tablespace:
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) 
from dba_tablespaces
where contents='UNDO'
/

Regards
Michel

Re: How to obtain DDL for REDO & UNDO? [message #325395 is a reply to message #325342] Fri, 06 June 2008 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For REDO files, I have this one in one my scripts (not SQL but might be useful):
SQL> Declare
  2    line    varchar2(1000);
  3    nblog   pls_integer;
  4    i       pls_integer;
  5    j     pls_integer;
  6  Begin
  7    i := 0;
  8    dbms_output.put_line('  LOGFILE');
  9    Select count(*) into nblog from v$log;
 10    For cgroup in (select group#, members, bytes from v$log) loop
 11      i := i + 1;
 12      j := 0;
 13      line := '    GROUP ' || cgroup.group#;
 14      If cgroup.members > 1 then
 15        line := line || ' (';
 16        dbms_output.put_line (line);
 17        line := '';
 18      End if;
 19      For log in (select member from v$logfile where group#=cgroup.group#)
 20      loop
 21        j := j + 1;
 22        If cgroup.members > 1 then
 23          line := '      ';
 24        Else
 25          line := '';
 26        End if;
 27        line := line || '''' || log.member || '''';
 28        If j < cgroup.members then line := line || ','; end if;
 29        dbms_output.put_line (line);
 30      End loop;
 31      If cgroup.members > 1 then line := '      )';
 32      Else line := ''; end if;
 33      line := line || ' SIZE ';
 34      If mod (cgroup.bytes, 1024*1024) = 0 then
 35        line := line || to_char(cgroup.bytes/(1024*1024)) || 'M';
 36      Elsif mod (cgroup.bytes, 1024) = 0 then
 37        line := line || to_char(cgroup.bytes/1024) || 'K';
 38      Else
 39        line := line || to_char(cgroup.bytes);
 40      End if;
 41      If i < nblog then line := line || ','; end if;
 42      dbms_output.put_line (line);
 43    End loop;
 44  End;
 45  /
  LOGFILE
    GROUP 1 (
      'C:\ORACLE\BASES\MIKA\RL_G1_1.RDO',
      'C:\ORACLE\ARCHIVES\MIKA\RL_G1_2.RDO'
      ) SIZE 10M,
    GROUP 2 (
      'C:\ORACLE\BASES\MIKA\RL_G2_1.RDO',
      'C:\ORACLE\ARCHIVES\MIKA\RL_G2_2.RDO'
      ) SIZE 10M,
    GROUP 3 (
      'C:\ORACLE\BASES\MIKA\RL_G3_1.RDO',
      'C:\ORACLE\ARCHIVES\MIKA\RL_G3_2.RDO'
      ) SIZE 10M,
    GROUP 4 (
      'C:\ORACLE\BASES\MIKA\RL_G4_1.RDO',
      'C:\ORACLE\ARCHIVES\MIKA\RL_G4_2.RDO'
      ) SIZE 10M

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Fri, 06 June 2008 02:17]

Report message to a moderator

Re: How to obtain DDL for REDO & UNDO? [message #325428 is a reply to message #325395] Fri, 06 June 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In SQL:
SQL> with 
  2    data as (
  3      select g.group#, f.member, g.bytes,
  4             row_number () over (partition by g.group# order by null) rn_member,
  5             count(*) over (partition by g.group#) cnt_member
  6      from v$log g, v$logfile f
  7      where f.group# = g.group#
  8      order by g.group#
  9    )
 10  select decode(rn_member,
 11                1, 'alter database add logfile group '||group#||' (
 12     ',
 13                   '  ,') || 
 14         member ||
 15         decode(rn_member, 
 16                cnt_member, '
 17     ) size '||bytes||';',
 18                '') 
 19           ddl
 20  from data
 21  /
DDL
-----------------------------------------------------------------------------------
alter database add logfile group 1 (
   C:\ORACLE\BASES\MIKA\RL_G1_1.RDO
  ,C:\ORACLE\ARCHIVES\MIKA\RL_G1_2.RDO
   ) size 10485760;
alter database add logfile group 2 (
   C:\ORACLE\BASES\MIKA\RL_G2_1.RDO
  ,C:\ORACLE\ARCHIVES\MIKA\RL_G2_2.RDO
   ) size 10485760;
alter database add logfile group 3 (
   C:\ORACLE\BASES\MIKA\RL_G3_1.RDO
  ,C:\ORACLE\ARCHIVES\MIKA\RL_G3_2.RDO
   ) size 10485760;
alter database add logfile group 4 (
   C:\ORACLE\BASES\MIKA\RL_G4_1.RDO
  ,C:\ORACLE\ARCHIVES\MIKA\RL_G4_2.RDO
   ) size 10485760;

8 rows selected.

Regards
Michel
Re: How to obtain DDL for REDO & UNDO? [message #325590 is a reply to message #325342] Fri, 06 June 2008 11:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Michel,
THANKS again.
At times I feel as though I am an octopus who needs to juggle 12 balls at the same time.
I need to figure out how to change a tire on a car while it is still rolling down the freeway.

Re: How to obtain DDL for REDO & UNDO? [message #325591 is a reply to message #325590] Fri, 06 June 2008 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Laughing

By the way, maybe not very useful in these times but I didn't find a way to get ddl for rollback segments with dbms_metadata.
If ever you find it...

Regards
Michel
Re: How to obtain DDL for REDO & UNDO? [message #325593 is a reply to message #325342] Fri, 06 June 2008 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Not yet, but I have time to get creative.
I get to build a new 3 node RAC on all new hardware (which has not yet arrived).
In August I get to "migrate" a 7x24 production DB onto the new RAC without any downtime.

http://www.oracle.com/technology/oramag/oracle/05-jul/o45tuning.html
It has been many, many moons since I have had to even think about UNDO.
with *.undo_management='AUTO', it appears Oracle automagically manages the undo segments.

[Updated on: Fri, 06 June 2008 11:37] by Moderator

Report message to a moderator

Re: How to obtain DDL for REDO & UNDO? [message #325597 is a reply to message #325342] Fri, 06 June 2008 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
alter system set undo_tablespace = <new_undo_tablespace_name>;

The database must be in AUTO undo management mode (with the undo_management initialization parameter set to AUTO) 
in order for this parameter to be valid.

After the database comes up in auto undo management mode, Oracle Database selects the first available undo tablespace 
(or the one specified by the undo_tablespace parameter). Oracle Database dynamically adjusts the number of undo segments on and
offline in response to system activity. You can query the DBA_ROLLBACK_SEGS view to see the system-generated undo segments, as follows:

SQL> select owner, segment_name, tablespace_name from dba_rollback_segs;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME
------ ------------------------------ ------------------------------
PUBLIC _SYSSMU15$                     UNDOTBS02
PUBLIC _SYSSMU16$                     UNDOTBS02
PUBLIC _SYSSMU17$                     UNDOTBS02
PUBLIC _SYSSMU18$                     UNDOTBS02
PUBLIC _SYSSMU19$                     UNDOTBS02
PUBLIC _SYSSMU20$                     UNDOTBS02
PUBLIC _SYSSMU21$                     UNDOTBS02
PUBLIC _SYSSMU22$                     UNDOTBS02
PUBLIC _SYSSMU23$                     UNDOTBS02
PUBLIC _SYSSMU24$                     UNDOTBS02
...

However, other than looking at the names, there's nothing you can do with these segments—Oracle Database manages them completely.
The V$UNDOSTATS view lets you look at all statistics associated with the UNDO tablespace, including the tuned_undoretention value over time, as in:

SQL>select tuned_undoretention from v$undostat;


TUNED_UNDORETENTION
-------------------
                900
               2700
               2700

[Updated on: Fri, 06 June 2008 11:45] by Moderator

Report message to a moderator

Re: How to obtain DDL for REDO & UNDO? [message #325604 is a reply to message #325597] Fri, 06 June 2008 12:09 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, this is why I said "maybe not very useful in these times", who still use rollback segments?

Regards
Michel
Previous Topic: Oracle Predefined Roles
Next Topic: delete statement is hanging when undo reaches 12G space
Goto Forum:
  


Current Time: Fri Nov 29 21:39:59 CST 2024