How to obtain DDL for REDO & UNDO? [message #325342] |
Thu, 05 June 2008 18:37 |
|
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 #325395 is a reply to message #325342] |
Fri, 06 June 2008 02:16 |
|
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 |
|
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 #325597 is a reply to message #325342] |
Fri, 06 June 2008 11:44 |
|
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
|
|
|
|