Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the working mode of DG backup undo in Oracle". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what is the working mode of DG backup undo in Oracle" together!

1. Modify the undo table space of the primary and backup databases

1. Create undo tablespaces in the master repository (automatically synced to the backup repository)

SYS@prod>create undo tablespace smallundo datafile '/u01/app/oracle/oradata/prod/smallundo.dbf' size 2M;

Modify undo tablespaces

SYS@prod>alter system set undo_tablespace=smallundo;

2. Modify undo table space in backup repository//because backup repository is in redo only mode, undo_table space cannot be modified online

SYS@stddb>shutdown immediate

[oracle@service2 dbs]$ cd $ORACLE_HOME/dbs

Modify parameter file *.undo_tablespace='smallundo'

SYS@stddb>create spfile from pfile

SYS@stddb>startup

Master Library:

SYS@prod>show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string SMALLUNDO

Reserve:

SYS@stddb>show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string smallundo

II: Testing

1. Create a test table in the main library:

SYS@prod>create table test (id number);

SYS@prod>insert into test(id) values(1);

SYS@prod>commit;

2. Simulate long-term query operations in the backup library:

SYS@stddb>variable rfc refcursor

SYS@stddb>execute open :rfc for select * from test;

3. Perform a circular update operation on the master library:

SYS@prod>

begin

for i in 1.. 20000 loop

update test set id = 3;

commit;

end loop;

end;

4. Get the query results in the backup database:

SYS@stddb>print :rfc

ERROR:

ORA-01555: snapshot too old: rollback segment number 13 with name"_SYSSMU13_2332596898$" too small

III: Conclusion

ORA-01555 appearing in the query statement executed in the backup library is the same as ORA-01555 appearing in the main library because the undo blocks of the main library and the backup library are real-time synchronized.

In this test, the undo table space that is not automatically extended is used. Since the table space cannot be automatically extended, it will give priority to keeping Active available space and overwrite the undo data that has already submitted transactions. Even if the retention time of undo retention is not met, ORA-01555 error will occur.

ORA-01555 error occurs when a SQL statement for a long query fails to obtain a pre-image construct CR block from undo

For example:

Session A executes a query statement to query 1 billion rows of data. Session B executes a delete statement to delete 100 million rows of data, and then commits it. When the transaction of Session B is successfully submitted, Data A queries the 100 million rows of data. At this time, undo data is needed to construct consistent reads. If undo date is overwritten at this time, Ora-01555 error will occur.

CR Block Structure of Reserve Library:

When the master and standby are synchronized, if the master database performs a transaction, but this transaction is not committed, query in the standby database, and need to construct a CR block to satisfy the query, and generate a CR block by combining the current block and the undo block.

//Same CR pattern as main library, it can also be understood that the undo used for query on backup library and query on main library is the same.

Test:

SYS@prod>select * from test;

ID

----------

3

SYS@stddb>select object_name,object_id from dba_objects where object_name='TEST' and owner='SYS';

//query object_id of test table

OBJECT_NAME OBJECT_ID

-------------------- ----------

TEST 88641

Make a modification in the main library:

SYS@prod>update test set id = 1;

1 row updated.

Query the block status in the master library and backup library:

SYS@prod>select obj,state from x$bh where state = 3 and obj=88641;

No rows selected.

SYS@stddb>select obj,state from x$bh where state = 3 and obj=88641;

No rows selected.

Perform query operations:

SYS@stddb>select * from test;

ID

----------

3

Query the test table in the primary and secondary libraries to construct CR blocks:

SYS@prod>select obj,state from x$bh where state = 3 and obj=88641;

OBJ STATE

---------- ----------

88641 3

88641 3

SYS@stddb>select obj,state from x$bh where state = 3 and obj=88641;

OBJ STATE

---------- ----------

88641 3

88641 3

3 is CR mode

The redo mode of backup library:

When the redo log of the primary repository switches, the redo log of the backup repository will also switch, but it has no meaning. It is only synchronization, and no warning log is recorded.

SYS@stddb>select group#,status from v$log;

GROUP# STATUS

---------- ----------------

1 CURRENT

2 CLEARING

3 CLEARING

The redo of the backup library has only two states: current and clearing.

Thank you for reading, the above is "Oracle DG backup undo working mode is what" content, after the study of this article, I believe we have a deeper understanding of Oracle DG backup undo working mode is what this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.