Database Sync

기본환경

• Oracle Database Version : Oracle Database EE 19c + RU19.14
• Oracle Database Type : Single
• AWS EC2 Instacne Type : r5.large (DB Instance Name : TEST)
• AWS RDS Instacne Type : db.r5.large (DB Instance Name : RDSTST)
• 성능 Test Tool : Swingbench

select instance_name, version_full, archiver, database_type from v$instance;

주요 Parameter 비교

select name, value
from v$spparameter
where value is not null
and name like '%target' or name = 'processes' or name = 'open_cursors'
order by 2
;

Swingbench로 초기 Data 적재 전/후 Tablespace 비교

초기 데이터 적재 전

select ddf.tablespace_name, ddf.file_name, round(ddf.bytes/1024/1024,2) MB, ddf.autoextensible, ddf.increment_by, dts.bigfile
from dba_data_files ddf, dba_tablespaces dts
where ddf.tablespace_name =  dts.tablespace_name
;

초기 데이터 적재 후

Default Tablespace Type

select * from DATABASE_PROPERTIES
where property_name='DEFAULT_TBS_TYPE'
;

AWS RDS for Oracle 생성 후 생성된 Tablespace 목록

select ddf.tablespace_name, ddf.file_name, round(ddf.bytes/1024/1024,2) MB, ddf.autoextensible, ddf.increment_by, dts.bigfile
from dba_data_files ddf, dba_tablespaces dts
where ddf.tablespace_name =  dts.tablespace_name
;

Big Tablespace와 Small Tablespace 생성

--기본값이 Bigfile인 관계로 Bigfile로 생성
CREATE TABLESPACE bigfiletest1 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G ;

CREATE BIGFILE TABLESPACE bigfiletest2 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G ;

--Smallfile을 명시하면 Smallfile로 생성 됨
CREATE SMALLFILE TABLESPACE smaillfiletest2 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G ;

select ddf.tablespace_name, ddf.file_name, round(ddf.bytes/1024/1024,2) MB, ddf.autoextensible, ddf.increment_by, dts.bigfile
from dba_data_files ddf, dba_tablespaces dts
where ddf.tablespace_name =  dts.tablespace_name
;

Default Tablespace Type을 Small file로 변경 (안됨)

alter database set default SMALLFILE tablespace;

Controlfile Reuse 및 DB resetlogs Open

ORACLE_BASE from environment = /rdsdbbin
Spfile /rdsdbbin/oracle/dbs/spfileRDSTSTEE.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
2022-XX-XXT04:02:13.257388+00:00
CREATE CONTROLFILE  REUSE SET DATABASE "RDSTSTEE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_1_k01sjh94_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 2 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_2_k01sjgqb_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 3 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_3_k01sjg6o_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 4 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_4_k01sjj8b_.log'  SIZE 128M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_system_k01sj5pg_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_sysaux_k01sj821_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_undo_t1_k01sj8k4_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_users_k01sj8pj_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_rdsadmin_k01toxo8_.dbf'
CHARACTER SET AL32UTF8
2022-XX-XXT04:02:13.257588+00:00
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
.... (PID:5591): RT: Role transition work is not done
Expanded controlfile section 32 from 31 to 128 records
Requested to grow by 97 records; added 3 blocks of records
.... (PID:5591): Redo network throttle feature is disabled at mount time
2022-XX-XXT04:02:13.361552+00:00
Successful mount of redo thread 1, with mount id 4185116805
Completed: CREATE CONTROLFILE  REUSE SET DATABASE "RDSTSTEE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_1_k01sjh94_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 2 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_2_k01sjgqb_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 3 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_3_k01sjg6o_.log'  SIZE 128M BLOCKSIZE 512,
  GROUP 4 '/rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_4_k01sjj8b_.log'  SIZE 128M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_system_k01sj5pg_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_sysaux_k01sj821_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_undo_t1_k01sj8k4_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_users_k01sj8pj_.dbf',
  '/rdsdbdata/db/RDSTSTEE_A/datafile/o1_mf_rdsadmin_k01toxo8_.dbf'
CHARACTER SET AL32UTF8
2022-XX-XXT04:02:13.570187+00:00
ALTER DATABASE OPEN RESETLOGS
2022-XX-XXT04:02:13.594227+00:00
RESETLOGS after incomplete recovery UNTIL CHANGE 754595 time XX/XX/2022 04:01:22
.... (PID:5596): Clearing online redo logfile 1 /rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_1_k01sjh94_.log
.... (PID:5596): Clearing online redo logfile 3 /rdsdbdata/db/RDSTSTEE_A/onlinelog/o1_mf_3_k01sjg6o_.log
Clearing online log 1 of thread 1 sequence number 0
Clearing online log 3 of thread 1 sequence number 0

Temp tablespace tempfile add

2022-XX-XXT04:07:21.305338+00:00
ALTER TABLESPACE TEMP ADD TEMPFILE
Completed: ALTER TABLESPACE TEMP ADD TEMPFILE
ALTER TABLESPACE TEMP AUTOEXTEND ON MAXSIZE 100G
Completed: ALTER TABLESPACE TEMP AUTOEXTEND ON MAXSIZE 100G