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
