버전 비교

이전 버전 2

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

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

Snapshot Standby Database로 전환

Standby

DB에 redo apply 비활성화

DB를 snapshot standby로 전환

코드 블럭
titleStandby DB에 Redo apply 해제Convert database 명령으로 역할 전환
linenumberstrue
EDITCONVERT DATABASE [standby 글로벌 DB명] TO SET STATE=apply-offSNAPSHOT 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 488 seconds ago)


DGMGRL> EDITCONVERT DATABASE 'orcl_stby' TO SET STATE=apply-off;
Succeeded.

DGMGRL> SHOW DATABASE orcl_stby

Database - orcl_stby

  Role:          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     PHYSICAL STANDBY
- Primary Intendeddatabase
 State:    orcl_stby APPLY-OFF
 Snapshot  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;

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
펼치기
서식 미적용
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          DATABASE_ROLE        8896056 bytes
Variable Size      SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl_stby       318767104  bytes
Database Buffers  orcl        922746880 bytes
Redo Buffers       OPEN         7880704 bytes
Database mounted.

SQL> setREAD linesWRITE 200
SQL> col db_unique_name form a20
SQL> col instance_name form a20
SQL> col statusSNAPSHOT formSTANDBY a15
SQL> col open_mode form a20
SQL> col database_role form a20
SQL> col switchover_status form a20
SQL> SELECT 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_stby            orcl     orcl            MOUNTED     OPEN    MOUNTED        READ WRITE     PHYSICAL STANDBY     NOT ALLOWED

Snapshot Standby로 전환

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

Database altered.
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_

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    

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        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에서는 테스트 데이터를 조회할 수 없습니다.


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

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
                OPEN_MODE       *
ERROR at line 1:
ORA-00942: table orDATABASE_ROLE 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:SWITCHOVER_STATUS
-------------------- -------------------- --------------- -------------------- -------------------- --------------------
orcl                 orcl         PHYSICAL STANDBY
  Intended State:    OPEN APPLY-OFF
  Transport Lag:      1 minute 33READ secondsWRITE (computed 0 seconds ago)
  Apply Lag:    PRIMARY      (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):TO STANDBY

SQL> select * from SNAPSHOT.SNAP_TEST;
select * from SNAPSHOT.SNAP_TEST
    orcl

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

Database Status:
WARNING

DGMGRL> EDIT DATABASE orcl_stby SET STATE=apply-on;
 *
ERROR at line 1:
ORA-00942: table or view does not exist

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

Physical Standby Database로 복원

Standby DB를 Physical Standby로 복원

코드 블럭
titleConvert database 명령으로 역할 전환
linenumberstrue
CONVERT DATABASE [standby 글로벌 DB명] TO PHYSICAL STANDBY;
펼치기
서식 미적용
DGMGRL> CONVERT DATABASE 'orcl_stby' TO PHYSICAL STANDBY;
Converting database "orcl_stby" to a Physical Standby database, please wait...
Operation requires shut down of instance "orcl" on database "orcl_stby"
Shutting down instance "orcl"...
Connected to "orcl_stby"
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcl_stby"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_stby"
Database mounted.
Connected to "orcl_stby"
Continuing to convert database "orcl_stby" ...
Database "orcl_stby" converted successfullySucceeded.

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

Redo Apply를 활성화시키면 Open Mode가 READ ONLY에서 READ ONLY WITH APPLY로 변경된 Physical Standby로 전환하면서 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
orcl_stby            orcl                 OPEN            READ ONLY WITH APPLY PHYSICAL STANDBY     NOT ALLOWED

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

SQL> READselect ONLYusername WITHfrom APPLYdba_users PHYSICALwhere STANDBYusername = 'SNAPSHOT';

no rows  NOT ALLOWEDselected