[12C] database flashback and data guard

Time:2021-2-23

In the data guard environment, the standby database can be opened in read-write mode by using the flashback feature of the database, which is used to perform some kind of test work on the primary database. After the test, the database can be flashed back to the previous state as a backup to continue to provide protection services.

1 flash back to database in standby database

1) Cancel standby database hosting recovery

SQL> alter database recover managed standby database cancel;



Database altered.

2) Setting the flashback recovery area of standby database

SQL> show parameter db_recover



NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

area

db_recovery_file_dest_size big integer 4560M

3) Start flashback characteristics

SQL> startup mount

ORACLE instance started.



Total System Global Area 1644167168 bytes

Fixed Size 2925024 bytes

Variable Size 1073745440 bytes

Database Buffers 553648128 bytes

Redo Buffers 13848576 bytes

Database mounted.

SQL> alter database flashback on;



Database altered.



SQL> select flashback_on from v$database;



FLASHBACK_ON

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

YES

4) Create a guaranteed restore point

SQL> create restore point before_opem_rw guarantee flashback database;



Restore point created.

5) Set the destination of log transfer from primary database to standby database to delay state

SQL> show parameter log_archive_dest_2



NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=shhai LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,P

RIMARY_ROLE)

DB_UNIQUE_NAME=orcldg

SQL> show parameter log_archive_dest_state_2



NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

SQL> alter system set log_archive_dest_state_2='defer';



System altered.

6) Activate the physical standby database, and convert the standby database into an independent database

SQL> alter database activate physical standby database;



Database altered.

SQL> alter database open;



Database altered.



SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

PRIMARY READ WRITE YES

7) Perform various operations such as testing in the activated standby database

8) After the test, flash the database back to the guaranteed restore point

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.



Total System Global Area 1644167168 bytes

Fixed Size 2925024 bytes

Variable Size 1073745440 bytes

Database Buffers 553648128 bytes

Redo Buffers 13848576 bytes

Database mounted.

SQL> flashback database to restore point before_opem_rw;



Flashback complete.

SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

PRIMARY MOUNTED YES

At this point, the database still makes the independent database, but the state returns to the restore point.

9) Convert database to standby database

SQL> alter database convert to physical standby ;



Database altered.



SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

PHYSICAL STANDBY MOUNTED YES



SQL> alter database open;



Database altered.

10) Enable log transfer from primary database to standby database

SQL> alter system set log_archive_dest_state_2='enable';



System altered.

11) Start the log application of Physical Standby Database

SQL> alter database recover managed standby database disconnect from session;



Database altered.



SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

PHYSICAL STANDBY READ ONLY WITH APPLY YES

The above demonstration shows how to create a restore point, activate the standby database, open it in read-write mode, conduct a series of tests, restore it by using flashback technology, and then convert it into a standby database. The whole process is cumbersome, and after the standby database is opened in read-write mode, it actually exists as an independent database, but during this period, the database stops receiving redo data from the main database To a certain extent, the protection of the primary database is lost. Is there a way to continue to receive redo logs when the standby database is in the read-write state, just without using it?

Snapshot database can do this, which will be demonstrated below.

2 snapshot standby database

1) Cancel standby database hosting recovery

SQL> alter database recover managed standby database cancel;



Database altered.

2) Convert standby database to snapshot database

SQL> alter database convert to snapshot standby;



Database altered.

SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

SNAPSHOT STANDBY MOUNTED YES

3) Open database

SQL> alter database open;



Database altered.



SQL> select database_role,open_mode,flashback_on from v$database;



DATABASE_ROLE OPEN_MODE FLASHBACK_ON

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

SNAPSHOT STANDBY READ WRITE YES

4) Perform a series of tests in the snapshot database;

5) Converting snapshot database to physical backup database

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.



Total System Global Area 1644167168 bytes

Fixed Size 2925024 bytes

Variable Size 1073745440 bytes

Database Buffers 553648128 bytes

Redo Buffers 13848576 bytes

Database mounted.

SQL> alter database convert to physical standby;



Database altered.



SQL> alter database open;



Database altered.

6) Standby database start hosting recovery

SQL> alter database recover managed standby database disconnect from session;



Database altered.

7) Modify data in main database

SQL> update scott.emp set comm=1000 where empno=7369;



1 row updated.



SQL> commit;



Commit complete.



SQL> select * from scott.emp where empno=7369;



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 1000 20

8) Verification of backup database, consistent.

SQL> select * from scott.emp where empno=7369;



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 1000 20

 

Recommended Today

Practice analysis of rust built-in trait: partialeq and EQ

Abstract:Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. This article is shared from Huawei cloud community《Analysis of rust built-in trait: partialeq and EQ》Author: debugzhang Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. Some traits can be automatically […]