In today’s article we will talk about Allowing Transactions in Logical Standby.
When Logical Standby Database is installed, no user other than SYS can WRITE even if they have dba authority. They require authorization to do so. This authority is given as follows.
1.We are questioning the GUARD status of the database.
|
1 2 3 4 5 6 7 |
[Logical-1] SQL> column guard_status format a15 SQL> select guard_status from v$database; GUARD_STATUS --------------- ALL |
Here;
ALL: Except for SYS, no user can WRITE any object.
STANDBY: Operation on any object processed by SQL APPLY processes is not allowed.
NONE: There is normal security. Whoever has the authority to act does it.
2. We grant the DBA authorization to the user.
|
1 2 3 4 |
[Logical-1] SQL> grant dba to test; Grant succeeded. |
3. The user tries to delete a row in the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[Logical-1] SQL> conn test/test Connected. SQL> show user USER is "TEST" SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> delete test.regions_yedek where region_id = 1; delete test.regions_yedek where region_id = 1 * ERROR at line 1: ORA-16224: Database Guard is enabled |
4. We pull the GUARD status to STANDBY.
|
1 2 3 4 5 6 7 8 9 10 |
[Logical-1] SQL> ALTER DATABASE GUARD STANDBY; Database altered. SQL> delete test.regions_yedek where region_id = 1; delete test.regions_yedek where region_id = 1 * ERROR at line 1: ORA-16224: Database Guard is enabled |
5. The GUARD status is set to NONE to ensure that the transaction is completed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[Logical-1] SQL> ALTER DATABASE GUARD NONE; Database altered. SQL> delete test.regions_yedek where region_id = 1; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 2 Americas 3 Asia 4 Middle East and Africa |
