이 페이지의 이전 버전을 보고 있습니다. 현재 버전 보기.

현재와 비교 페이지 이력 보기

« 이전 버전 2 다음 »

1. Snapshot Standby Database로 전환

A) Standby DB에 redo apply 비활성화

Standby DB에 Redo apply 해제
EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-off;
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 48 seconds ago)

DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-off;
Succeeded.

DGMGRL> SHOW DATABASE orcl_stby

Database - orcl_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    orcl

Database Status:
SUCCESS


B) Standby DB를 mount 모드로 전환

Standby DB를 mount 상태로 전환
shutdown immediate;
startup mount;

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1258290744 bytes
Fixed Size                  8896056 bytes
Variable Size             318767104 bytes
Database Buffers          922746880 bytes
Redo Buffers                7880704 bytes
Database mounted.

SQL> set lines 200
SQL> col db_unique_name form a20
SQL> col instance_name form a20
SQL> col status form a15
SQL> col open_mode form a20
SQL> col database_role form a20
SQL> col switchover_status form a20
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                 MOUNTED         MOUNTED              PHYSICAL STANDBY     NOT ALLOWED


C) Snapshot Standby로 전환

Snapshot Standby 로 전환
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.


D) Standby DB를 read write 모드로 오픈

Standby DB를 read write 모드로 open
ALTER DATABASE OPEN READ WRITE;
SQL> ALTER DATABASE OPEN READ WRITE;

Database altered.


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를 mount로 전환

Standby DB를 mount로 전환
shutdown immediate;
startup mount;
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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1258290744 bytes
Fixed Size                  8896056 bytes
Variable Size             318767104 bytes
Database Buffers          922746880 bytes
Redo Buffers                7880704 bytes
Database mounted.


B) Standby DB를 Physical Standby로 원복

Physical Standby 로 전환
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.


Standby DB를 read only 모드로 오픈
ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

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 ONLY            PHYSICAL STANDBY     RECOVERY NEEDED

Physical Standby로 전환하면서 Snapshot Standby 모드에서 생성된 테스트 테이블과 유저가 제거된 것을 확인할 수 있습니다.

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

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

no rows selected


C) Standby DB에 redo apply 활성화

Standby DB에 redo apply 활성화
EDIT DATABASE [standby 글로벌 DB명] SET STATE=apply-on;
DGMGRL> SHOW DATABASE orcl_stby

Database - orcl_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      1 minute 33 seconds (computed 0 seconds ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    orcl

  Database Warning(s):
    ORA-16855: transport lag has exceeded specified threshold

Database Status:
WARNING

DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-on;
Succeeded.

DGMGRL> SHOW DATABASE orcl_stby

Database - orcl_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 48.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    orcl

Database Status:
SUCCESS

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 17 seconds ago)

Redo Apply를 활성화시키면 Open Mode가 READ ONLY에서 READ ONLY WITH APPLY로 변경된 것을 확인할 수 있습니다.

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 ONLY WITH APPLY PHYSICAL STANDBY     NOT ALLOWED


  • 레이블 없음