Creating physical standby database
1. standby controlfile 생성(At Primary DB)
SQL>alter database create standby controlfile as '/data1/standby.ctl';
SQL>shutdown immediate;
2. Copy datafiles, standby controfile, archive logs, online logs from Primary to Standby
-- 파라미터 파일은 미리 복사한 후 standby db에 맞게 수정해 놓아야한다.
-- control file 복사
cp /data1/standby.ctl /data1/oradata/STBY/control01.ctl
cp /data1/standby.ctl /data1/oradata/STBY/control02.ctl
cp /data1/standby.ctl /data1/oradata/STBY/control03.ctl
3. Start and Mount Standby instance(At Standby DB)
sqlplus "/as sysdba"
SQL>startup nomount
SQL>alter database mount standby database;
-- standby redo log 생성(online redo log와 같은 크기로 생성해야한다.)
SQL>alter database add standby logfile '/data1/oradata/standby01.log' size 1M;
SQL>alter database add standby logfile '/data1/oradata/standby02.log' size 1M;
Note: 아래 3가지 방법 중 마지막 방법을 사용한다.
SQL>recover standby database; (normal recovery mode)
...
Specify log:{<RET>=suggested | filename | auto | cancel}
- or -
SQL>recover managed standby database; (by forground process. waiting)
- or -
SQL>recover managed standby database disconnect (from session) (parallel n); (by MRP process)
4. 확인
(At Primary)
SQL>select database_role, protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
-------------------------------------------
PRIMARY MAXIMUM PERFORMANCE
(At Standby)
SQL>select database_role, protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
------------------------------------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE
5. Archive 전송 Test
(At Primary)
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
...
SQL>col error format a20
SQL>select dest_id, status, error from v$archive_dest
where dest_id <=2;
DEST_ID STATUS ERROR
-------------------------------------
1 VALID
2 VALID
(At Standby)
archive log file 확인
6. Data 전송 확인 Test
(At Primary)
SQL>create table hr.test(a number);
SQL>insert into hr.test values(100);
SQL>commit;
SQL>alter system archive log current;
(At Standby)
SQL>recover managed standby database cancel;
SQL>alter database open read only;
SQL>select * from hr.test;
A
---------
100
-- Read only => managed recovery mode 전환
SQL>shutdown
SQL>startup nomount
SQL>alter database mount standby database;
SQL>recover managed standby database disconnect; | |