I am testing some backup utility working with Oracle DB 19c where Im trying to generate some archive logs so that it is able to be deleted after a certain size.

Is there some way to quickly generate redo logs via artificially creating load so that they are pushed to archivelogs?

CodePudding user response：

One way to generate redo would be the following

• Be sure to have the tablespace with logging mode or the table created with the logging option. If the tablespace was created without specifying any logging mode, the default mode is logging. Same applied for the table.
• To avoid issues with storage, just truncate the table in each main loop.
• Be sure that you have enough space for at least one loop iteration, thereby you won't get any error.

In the example below, we put the tablespace in force logging mode , although it is not necessary. Then I create a test table with just three fields, but you can use as many as you want, just remember you need storage for at least one iteration.

I use dbms_random to generate random string values.

Example

alter tablespace users force logging; -- if the tablespace has nologging 

create table x ( c1 number, c2 varchar2(50), c3 varchar2(50) ) logging tablespace users ; -- table in logging mode 

declare
num_loops pls_integer := 10; -- use as many iterations as you want. 
begin
for r in 1..num_loops
loop
    for h in 1 .. 100000 -- I just define 100k for main loop to avoid undo issues
    loop
        insert into x values ( h , dbms_random.string('X',50), dbms_random.string('X',50) ) ;
    end loop;
    commit;
    execute immediate ' truncate table x reuse storage ' ;
end loop;
end;
/