버전 비교

이전 버전 1

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 2

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.

Snapshot Standby Database로 전환

Standby DB에 redo apply 비활성화

코드 블럭
titleStandby DB에 Redo apply 해제
linenumberstrue
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


Standby DB를 mount 모드로 전환

코드 블럭
titleStandby DB를 mount 상태로 전환
linenumberstrue
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


Snapshot Standby로 전환

코드 블럭
titleSnapshot Standby 로 전환
linenumberstrue
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
펼치기
서식 미적용
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.


Standby DB를 read write 모드로 오픈

코드 블럭
titleStandby DB를 read write 모드로 open
linenumberstrue
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


Standby Database 테스트

기존 데이터 존재 여부 확인

코드 블럭
titlePrimary DB에 테스트 유저 존재 여부 확인
linenumberstrue
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 데이터베이스에만 존재하면 됩니다.

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

코드 블럭
titleStandby DB에 유저 및 테이블 생성 후 테스트 데이터 입력
linenumberstrue
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 데이터에서만 조회되어야 합니다.

Primady DB에서 조회 불가 확인

코드 블럭
titlePrimary DB에서 테스트 테이블 조회 여부 확인
linenumberstrue
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에서는 테스트 데이터를 조회할 수 없습니다.

Physical Standby Database로 원복

Standby DB를 mount로 전환

코드 블럭
titleStandby DB를 mount로 전환
linenumberstrue
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.


Standby DB를 Physical Standby로 원복

코드 블럭
titlePhysical Standby 로 전환
linenumberstrue
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
펼치기
서식 미적용
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.


코드 블럭
titleStandby DB를 read only 모드로 오픈
linenumberstrue
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


Standby DB에 redo apply 활성화

코드 블럭
titleStandby DB에 redo apply 활성화
linenumberstrue
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