19c DG Silent : 09. Snapshot Standby Database

1. Snapshot Standby Database로 전환

A) Standby DB를 snapshot standby로 전환

Convert database 명령으로 역할 전환

CONVERT DATABASE [standby 글로벌 DB명] TO SNAPSHOT STANDBY;

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl      - Primary database
    orcl_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 8 seconds ago)


DGMGRL> CONVERT DATABASE 'orcl_stby' TO SNAPSHOT STANDBY;
Converting database "orcl_stby" to a Snapshot Standby database, please wait...
Database "orcl_stby" converted successfully

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl      - Primary database
    orcl_stby - Snapshot standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 50 seconds ago)

Standby database가 snapshot standby로 롤이 변경된 것을 확인할 수 있습니다.

SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;

DB_UNIQUE_NAME       INSTANCE_NAME        STATUS          OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl_stby            orcl                 OPEN            READ WRITE           SNAPSHOT STANDBY     NOT ALLOWED

2. Standby Database 테스트

A) 기존 데이터 존재 여부 확인

Primary DB에 테스트 유저 존재 여부 확인

select username from dba_users where username = 'SNAPSHOT';

SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;

DB_UNIQUE_NAME       INSTANCE_NAME        STATUS          OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl                 orcl                 OPEN            READ WRITE           PRIMARY              FAILED DESTINATION

SQL> select username from dba_users where username = 'SNAPSHOT';

no rows selected

Snapshot Standby Database 기능을 테스트하기 위한 계정은 standby 데이터베이스에만 존재하면 됩니다.

B) Standby DB에서 테스트 데이터 생성

Standby DB에 유저 및 테이블 생성 후 테스트 데이터 입력

create user snapshot identified by snapshot;
GRANT dba TO snapshot;
CREATE TABLE SNAPSHOT.SNAP_TEST (id varchar2(5), name varchar2(20));
INSERT INTO SNAPSHOT.SNAP_TEST VALUES ('001','scott');
COMMIT:
select * from SNAPSHOT.SNAP_TEST;

SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;

DB_UNIQUE_NAME       INSTANCE_NAME        STATUS          OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl_stby            orcl                 OPEN            READ WRITE           SNAPSHOT STANDBY     NOT ALLOWED

SQL> CREATE USER snapshot IDENTIFIED BY snapshot;

User created.

SQL> GRANT dba TO snapshot;

Grant succeeded.

SQL> CREATE TABLE snapshot.snap_test (id VARCHAR2(5), name VARCHAR2(20));

Table created.

SQL> INSERT INTO snapshot.snap_test VALUES ('001','scott');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from SNAPSHOT.SNAP_TEST;

ID    NAME                
----- --------------------
001   scott

Standby 데이터베이스에 테스트를 위한 계정을 만들고 권한을 부여한 후에 테스트를 위한 테이블과 데이터를 입력합니다.

이 테이블은 Standby 데이터에서만 조회되어야 합니다.

C) Primady DB에서 조회 불가 확인

Primary DB에서 테스트 테이블 조회 여부 확인

select * from SNAPSHOT.SNAP_TEST;

SQL> SELECT db_unique_name, instance_name, status, open_mode, database_role, switchover_status FROM v$database d, v$instance i;

DB_UNIQUE_NAME       INSTANCE_NAME        STATUS          OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl                 orcl                 OPEN            READ WRITE           PRIMARY              TO STANDBY

SQL> select * from SNAPSHOT.SNAP_TEST;
select * from SNAPSHOT.SNAP_TEST
                       *
ERROR at line 1:
ORA-00942: table or view does not exist

Snapshot Standby Database에만 생성된 테이블이므로 Primary DB에서는 테스트 데이터를 조회할 수 없습니다.

3. Physical Standby Database로 복원

A) Standby DB를 Physical Standby로 복원