Re: 오늘의 마지막 문제. 이수장 평가 5번 문제

[22기_정민수]

-- 데이터 가드 브로커 
-----------------------------------------------------------------------------------------------------------------------------
-- NERO     (PRIMARY DB)
-- NEROSTBY (STANDBY DB)
-----------------------------------------------------------------------------------------------------------------------------
-- 0. NERO, NEROSTBY: SPFILE로 올리기.
-- [공통 점검] SQL 
SHOW PARAMETER SPFILE;

-- spfile 생성
CREATE SPFILE FROM PFILE;

-----------------------------------------------------------------------------------------------------------------------------
-- SQL (SYS@NEROSTBY)
-----------------------------------------------------------------------------------------------------------------------------
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-----------------------------------------------------------------------------------------------------------------------------
-- SQL (SYS@NERO)
-----------------------------------------------------------------------------------------------------------------------------
SHUTDOWN IMMEDIATE;
STARTUP;

-----------------------------------------------------------------------------------------------------------------------------
-- [공통 점검] SQL 
-----------------------------------------------------------------------------------------------------------------------------
SHOW PARAMETER SPFILE;

-----------------------------------------------------------------------------------------------------------------------------
-- (Shell)리스너 추가: 브로커 사용을 위한 리스너 정보 변경 (SID_LIST_LISTENE)
-----------------------------------------------------------------------------------------------------------------------------
# 리스너 백업
cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/20260326_listener.ora

vi listener.ora
-----------------------------------------------------------------------------------------------------------------------------
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = NERO_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = NERO)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = NERO)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = NERO)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = NEROSTBY_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = NEROSTBY)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = NEROSTBY)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = NEROSTBY)
    )
  )

-----------------------------------------------------------------------------------------------------------------------------
# 재가동 
# lsnrctl reload
lsnrctl stop
lsnrctl start
-----------------------------------------------------------------------------------------------------------------------------
-- SQL [SYS@NERO]
-----------------------------------------------------------------------------------------------------------------------------
ALTER system SET dg_broker_start=true SCOPE=both;

SHOW PARAMETER dg_broker_start

/*
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
*/

-----------------------------------------------------------------------------------------------------------------------------
-- SQL [SYS@NEROSTBY]
-----------------------------------------------------------------------------------------------------------------------------
ALTER system SET dg_broker_start=true SCOPE=both;

SHOW PARAMETER dg_broker_start

/*
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
*/

-----------------------------------------------------------------------------------------------------------------------------
-- 
-----------------------------------------------------------------------------------------------------------------------------
$ dgmgrl

DGMGRL> connect sys/oracle@NERO

-- Primary 등록
DGMGRL> 
CREATE configuration dg_config AS
PRIMARY DATABASE IS NERO
CONNECT identifier IS NERO;

-- Configuration "dg_config" created with primary database "nero"

-- Standby 추가
DGMGRL> 
add database NEROSTBY as
connect identifier is NEROSTBY
maintained as physical;

-- Configuration 활성화
DGMGRL> 
enable configuration;

>> Enabled.


DGMGRL> 
SHOW configuration;

>>  nero     - Primary database

DGMGRL> 
SHOW database NERO;
/*
Database - nero

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    NERO

Database Status:
SUCCESS
*/

DGMGRL>
SHOW database NEROSTBY;
/*
Database - nerostby

  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: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    NEROSTBY

Database Status:
SUCCESS

*/

DGMGRL> 
validate database NERO;
validate database NEROSTBY;


DGMGRL> 
switchover to NEROSTBY;


-----------------------------------------------------------------------------------------------------------------------------
-- 검수
-----------------------------------------------------------------------------------------------------------------------------
DGMGRL> 
show configuration;

DGMGRL> 
show database NEROSTBY;


DGMGRL> 
show database NERO;