Перевод physical standby в snapshot standby : Oracle

█ 10.03.2023 12:26

Для тестирования сриптов перед накатом на основную базу и для каких-то еще тестов, когда нужна текущая база в ее наиболее актуальном состоянии, начиная с 11 версии Oracle, standby можно перевести в snapshot standby, поработать в нем в режиме чтения-записи, после чего вернуть все в первоначальное состояние и продолжить накат. Последовательность следующая.
Проверяем, что стендбай правильный и в нужном режиме.

Код:

SQL@STANDBY> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
1 row selected.

Код:

SYS@STANDBY> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
1 row selected.

Останавливаем накат логов

Код:

SYS@STANDBY> alter database recover managed standby database cancel;
Database altered.

Включаем flashback

Код:

SYS@STANDBY> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@STANDBY> startup mount;
ORACLE instance started.
Total System Global Area 2656452608 bytes
Fixed Size 2161272 bytes
Variable Size 1291847048 bytes
Database Buffers 1073741824 bytes
Redo Buffers 288702464 bytes
Database mounted.
SYS@TESTER1 SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SYS@TESTER1 SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SYS@TESTER1 SQL> alter system set db_recovery_file_dest_size=1g;
System altered.
SYS@TESTER1 SQL> alter system set db_recovery_file_dest='/u03/testdb/TESTER1';
System altered.
SYS@TESTER1 SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u03/testdb/TESTER1
db_recovery_file_dest_size big integer 1G
SYS@TESTER1 SQL> alter database flashback on;
Database altered.
SYS@TESTER1 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
1 row selected.

Переключаем в snapshot standby

Код:

SYS@TESTER1 SQL> select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
SYS@TESTER1 SQL> alter database convert to snapshot standby;
Database altered.
SYS@TESTER1 SQL> alter database open;
Database altered.
SYS@TESTER1 SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
1 row selected.
SYS@TESTER1 SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME GUA
---------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_10/03/2012 06: YES  13:19

Здесь с базой творите всякие неприличные вещи, стараясь не выйти за пределы объема RFD.

И возвращаем обратно в physical standby

Код:

SYS@TESTER1 SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@TESTER1 SQL> startup mount
ORACLE instance started.
Total System Global Area 2656452608 bytes
Fixed Size 2161272 bytes
Variable Size 1291847048 bytes
Database Buffers 1073741824 bytes
Redo Buffers 288702464 bytes
Database mounted.
SYS@TESTER1 SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
1 row selected.
SYS@TESTER1 SQL> alter database convert to physical standby;
Database altered.
SYS@TESTER1 SQL> select database_role from v$database;
select database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SYS@TESTER1 SQL> select status from v$instance;
STATUS
------------
STARTED
1 row selected.
SYS@TESTER1 SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@TESTER1 SQL> startup
ORACLE instance started.
Total System Global Area 2656452608 bytes
Fixed Size 2161272 bytes
Variable Size 1291847048 bytes
Database Buffers 1073741824 bytes
Redo Buffers 288702464 bytes
Database mounted.
Database opened.
SYS@TESTER1 SQL>alter database recover managed standby database using current logfile disconnect;
SYS@TESTER1 SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
1 row selected.
SYS@TESTER1 SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
1 row selected.

█ 10.03.2023 12:39

И аналогичная конвертация, только с помощью брокера (dgmgrl)
Проверка flashback

Код:

SQL> show parameter db_recovery
NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
+FRA
db_recovery_file_dest_size           big integer
200G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on;
Database altered.
SQL>  select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

Проверка конфигурации DG

Код:

DGMGRL>  show configuration
Configuration - dg_config_somedb
  Protection Mode: MaxPerformance
  Members:
  SOMEDB      - Primary database
    SOMEDBDR    - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR   (status updated 46 seconds ago)
DGMGRL> validate database SOMEDBDR
  Database Role:     Physical standby database
  Primary Database:  SOMEDB
  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
  Flashback Database Status:
    SOMEDB  :  Off
    SOMEDBDR:  On
  Managed by Clusterware:
    SOMEDB  :  YES
    SOMEDBDR:  YES
DGMGRL> show configuration
Configuration - dg_config_somedb
  Protection Mode: MaxPerformance
  Members:
  SOMEDB      - Primary database
    SOMEDBDR    - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 6 seconds ago)

Переводим в snapshot standby

Код:

DGMGRL> convert database SOMEDBDR to snapshot standby;
Converting database "SOMEDBDR" to a Snapshot Standby database, please wait...
Database "SOMEDBDR" converted successfully
DGMGRL> show configuration
Configuration - dg_config_somedb
  Protection Mode: MaxPerformance
  Members:
  SOMEDB      - Primary database
    SOMEDBDR    - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 52 seconds ago)
SQL> select open_mode,database_role from v$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           SNAPSHOT STANDBY

Тут издеваемся над базой.

И переводим обратно snapshot standby в physical standby

Код:

DGMGRL> convert database SOMEDBDR to physical standby;
Converting database "SOMEDBDR" to a Physical Standby database, please wait...
Operation requires a connection to database "ne"
Connecting ...
Connected to "SOMEDB"
Connected as SYSDBA.
Oracle Clusterware is restarting database "SOMEDBDR" ...
Connected to "SOMEDBDR"
Connected to "SOMEDBDR"
Continuing to convert database "SOMEDBDR" ...
Database "SOMEDBDR" converted successfully
DGMGRL> show configuration;
Configuration - dg_config_somedb
  Protection Mode: MaxPerformance
  Members:
  SOMEDB      - Primary database
    SOMEDBDR    - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 4 seconds ago)
DGMGRL>